MDX Calculation - % of All

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]
 AS ([Measures].[Headcount],
[Age Band].[Age Band].[All Age Bands],
[Employee].[Employee].[All Employees],
.... etc

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?
LVL 15
derekkrommAsked:
Who is Participating?
 
derekkrommConnect With a Mentor Author Commented:
Ok, I figured out a workaround.

I totaled the measure at the lowest time grain in the SQL layer.

Then I added that as a measure to the cube twice: once with sum aggregation and once with count.

Then I created a calculated member that did sum / count to get back to the desired #.
0
All Courses

From novice to tech pro — start learning today.