Search code examples
mdxiccube

calculate the cumulative % of a hierarchy member in icCube | TopPercent but than different

With the function TopPerent, you can get the set of members - ordered top down - that comply to the % value provided.

I would like to switch this function and get the cumulative % given the member.

TopPercent in icCube, all countries in Excel ordered top down on Amount. Cum % caluclated. The BLUE highlighted values indicate the value I would like to obtain

Description of the image: TopPercent in icCube, all countries in Excel ordered top down on Amount. Cum % caluclated. The BLUE highlighted values indicate the value I would like to obtain

Solution

  • I hope it will be enough to understand the point:

    With 
Set [OrderedCity] as
Order([Customer].[City].[City].Members,[Measures].[Internet Sales Amount],DESC)    

Member [Measures].[Cum] as
Sum(    
    Head([OrderedCity],Rank([Customer].[City].CurrentMember,[OrderedCity])),
    [Measures].[Internet Sales Amount]
)

Member [Measures].[Cum %] as
[Measures].[Cum] / ([Customer].[City].[All],[Measures].[Internet Sales Amount]),
Format_String = "Percent"

Select
Non Empty [OrderedCity] on 1,
{[Measures].[Internet Sales Amount],[Measures].[Cum],[Measures].[Cum %]} on 0
From [Adventure Works]