MDX- Divide Each row by a value based on parent

Question

I am in a situation where I need to calculate Percentage for every fiscal year depending on distinct count of the rows.

I have achieved the distinct count (fairly simple task) for each year city-wise and reached till these 2 listings in cube.

The first listing is state wide distinct count for given year. Second listing is city wise distinct count for given year with percentage based on state-wide count for that year for that city.

My problem is that I need to prepare a calculated member for the percentage column for each given year.

For eg, In year 2009, City 1 has distinct count of 2697 and percentage raise of 32.94%. (Formula used= 2697/8187 ).

I tried with

 ([Measures].[Distinct Count])/(SUM(ROOT(),[Measures].[Distinct Count]))

but no luck.

Any help is highly appreciated.

Thanks in advance.

PS: City wide sum of year 2009 can never be equal to statewide distinct count of that year. This is because we are calculating the distinct count for city and state both.

Answer 1

You need to create a Region Hierarchy for this, like State -> City. The create a calculation like below. Then in the browser put your Hierarchy on the left and the sales and calculated percentage in values.

([Dim].[Region].CurrentMember, [Measures].[Salesamt]) /
iif(
    ([Dim].[Region].CurrentMember.Parent, [Measures].[Salesamt]) = 0,
    ([Dim].[Region].CurrentMember, [Measures].[Salesamt]),
    ([Dim].[Region].CurrentMember.Parent, [Measures].[Salesamt])
)