I am looking at a cube using excel. I have added two dimensions to row fields and one to the columns (date). Is there any calculation that will return the sum of a specific measure for one of the dimensions regardless of what is selected for the second dimension The dimension in question is a hierarchy and I want to get a sum for a certain level in the hierarchy.
I have tried SUM with ANCESTORS but it still only returns the sum as constrained by values selected for the second dimension.
Here is what I was trying:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Portfolio MV]
AS Case
When
IsEmpty([Measures].[Market Value])
Then 0
When
[Portfolio].[Portfolio Name].CurrentMember.Level Is
[Portfolio].[Portfolio Name].[(All)]
Then 1
Else
SUM(ANCESTORS([Portfolio].[Portfolio Name],1),[Measures].[Market Value])
End,
FORMAT_STRING = "#,#;-#,#;",
VISIBLE = 1 ;
How to get an absolute sum for all values in a certain level in a hierarchy
DavidC#2005
If you don't explicitly state in SUM what value of the second dimension you want to consider, you get the selected members.
However, if you do:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Portfolio MV]
AS Case
When
IsEmpty([Measures].[Market Value])
Then 0
When
[Portfolio].[Portfolio Name].CurrentMember.Level Is
[Portfolio].[Portfolio Name].[(All)]
Then 1
Else
SUM(ANCESTORS([Portfolio].[Portfolio Name],1),([Measures].[Market Value],[Second Dimension].[All Member]))
End,
FORMAT_STRING = "#,#;-#,#;",
VISIBLE = 1 ;
you then don't take into account the current member of the second dimension.
NET PR
I want the sum of all members in a given portfolio regardless of the second dimension. Regardless of any additional dimensions.