MDX Calculation - % of All
Posted on 2011-02-18
I'm trying to find an easier way to accomplish the following:
I have a calculated member called StaffingRate. It should return the % of Headcount as compared to the entire dataset based on where you've drilled.
So for example, lets say the total headcount for a given time period is 50. At the top level it should be 100%. If I drill into an org chart, it should return a % of the whole. Lets say a chart looks like A->B/C. B->D. C->E/F. A should always be 100%. If there is a Headcount of 10 at F, it should be 20% when I drill down to that level. I hope this makes sense.
The way I'm currently doing this is by first creating a calculated member called Headcount All Level that looks like:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Headcount All Level]
[Age Band].[Age Band].[All Age Bands],
Where I list out every "All" dimensional level. And then my Staffing Rate member = Headcount / Headcount All Level
There has to be a better/more efficient way to handle this, right?