Calculated member to calculate % of sales of a parent

• michielbijnen

  Ten Centuries

  Points: 1308

  More actions

  August 30, 2013 at 8:32 am

  #147870

  Hi,

  I have an issue with a calculated member in SSAS 2008R2. I think it's relatively simple but I can't get my hands on it. I am relatively new in MDX.

  I have a dimension [Projects] with a hierarchy called [Parent Project]. This hierarchy has the attributes [Parent Projectno] on top and then [Projectno].

  As measures I have [Sales] and [Cost of Sales] and a calculated member [Gross Margin] that calculates ([Measures].[Sales] - [Measures].[Cost of Sales]).

  So far no problems.

  What I would like to have is the ratio (as a percentage) of the gross margin (GM) per project to the gross margin of the parent project.

  So for example:

  Parent project 800 holding projects 810, 812 and parent project 900 holding projects 901, 902 and 903.

  GM project 810 = 2.000

  GM project 812 = 3.000

  GM parent project 800 = 5.000

  GM project 901 = 10.000

  GM project 902 = 5.000

  GM project 903 = 15.000

  GM parent project 900 = 30.000

  So when I drag the hierarchy [Parent Project] to the cube browser in BIDS and browse all parent projects the percentages per project should be (when I click to the project-level):

  810: 40% (=2000/5000)

  812: 60% (=3000/5000)

  800: 100%

  901: 33,3% (=10000/30000)

  902: 16,7% (=5000/30000)

  903: 50% (=15000/30000)

  900: 100%

  My MDX-expression in the Calculations-tab in BIDS shows this, but it does not give me the correct percentages on the project level.

  I have tried several scenarios but to no avail.

  The last 2 scenarios are:

  Scenario 1 (not working):

  CASE

  WHEN

  IsEmpty([Measures].[Gross Margin])

  THEN NULL

  When [Projects].[Parent Project].[Projectno].CurrentMember.Level Is

  [Projects].[Parent Project].[Projectno].[(All)]

  Then 1

  Else

  ( [Projects].[Parent Project].[Projectno].CurrentMember,

  [Measures].[Gross Margin] )

  /

  ( [Projects].[Parent Project].[Parent Projectno].CurrentMember,

  [Measures].[Gross Margin] )

  END

  Scenario 2 (not working):

  CASE

  WHEN

  IsEmpty([Measures].[Gross Margin])

  THEN NULL

  When [Projects].[Parent Project].[Projectno].CurrentMember.Level Is

  [Projects].[Parent Project].[Projectno].[(All)]

  Then 1

  Else ( [Projects].[Parent Project].[Projectno].CurrentMember,

  [Measures].[Gross Margin] )

  /

  ( [Projects].[Parent Project].[Projectno].CurrentMember.Parent,

  [Measures].[Gross Margin] )

  End

  What's wrong?

  Hope someone can help me out.

  Thanks!

  Michiel