CODEWITHSUNDEEP
mdx
Harry Leboeuf
Harry Leboeuf

Reputation: 59

MDX Calculate SUm at a fixed level in hierarchy

I Have a hieracy Defined a Level1 to Level5 and beneath that an Id. I would like to create a calculated member that always makes the sum at the Level4. currentMember.Parent works at Level5 but not at the Id Level.

What is the better way ?

Upvotes: 2

Views: 2804

Answers (1)

Harry Leboeuf
Harry Leboeuf

Reputation: 59

Ok, Found it, With the Ancestors you can specify at what leve you want it.

WITH MEMBER Measures.Temp AS
SUM(Ancestors([Master Product].[Product Tree].CurrentMember,[Master Product].[Product Tree].[IBS Level 4]), [Measures].[Qty Master Product])
SELECT NON EMPTY { [Measures].[Qty Master Product], [Measures].[Qty Cross Product], Measures.Temp } ON COLUMNS,
    NON EMPTY { [Master Product].[Product Tree].[IBS Level 5] } ON ROWS
 FROM [ITS Cross Sales]
WHERE ( [Complex].[By Country].[Complex].&[3],
        [Dates].[Calender].[Date].&[2013-03-17T00:00:00] )

Upvotes: 3

Related Questions