Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculated member to calculate % of sales of a parent Expand / Collapse
Author
Message
Posted Friday, August 30, 2013 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 2:53 PM
Points: 45, Visits: 143
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

Post #1490185
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse