Averaging Headcount RRS feed

  • Question

  • I need help in creating a calculation that will bring back average headcount over time. Here is what I have.

    I have a fact table that list a record for each employee who was in headcount at the end of each period (one record per month).  I have a dimension table to determine the time periods I am looking for.

    What the issue is if I bring in the count measure without the time dimension I get the sum of employees for the year. Not so desirable. It is not till you bring in the time dimension and look at it by period that the field makes any sense.  What I would like have is a calculation that displays the average headcount by year. Then then as they drill down the count for each period.  Additionally, I would like to use this calculation in MDX to display attrition rates.

    I am a complete novice at MDX coding. I am good in TSQL but that does not translate well.

    Can anyone point me in the right direction here? What would a MDX code look like that would handle this? Or maybe an article that will help in this.  The ones I found so far have not been helpful as I am just not savvy enough to figure out what it is doing.

    My dim date table has a primary key foreign key relationship on DateKey field with the fact table that identifies the records at period end. Also I have a hierarchy from year to quarter then to period that is working fine at the period level but ugly when I count on Year and Quarter. On year and Quarter is when that I want the averages to show not the sum totals.  

    Any help would be greatly appreciated. 

    Thursday, May 17, 2012 12:35 PM

All replies

  • Have you tried to take advantage of the AverageOfChildren Aggregate Function property for the headcount measure?  With the way you already have things setup, this sounds like it would work well for you.

    This can be changed by opening the cube in BIDS, finding the measure group that contains your measure, selecting the measure, then changing the Aggregate Function property to AverageOfChildren.  This would completely avoid the need for having to come up with some kind of MDX query to achieve the same.


    David Duncan Consultant I.B.I.S., Inc.

    Thursday, May 17, 2012 12:42 PM
  • Thanks are you referring to changing the measure to Average over time?  If so I tried that and it is not supported in the Standard edition of Server Analytics that I use.  Is there somewhere else in the DSV that I could change that or is that only at the cube level?

    Thursday, May 17, 2012 3:38 PM
  • You can write a SCOPE statement to override the default (Sum) aggregation for the measure for the levels other than the leaf level in your hierarchy. It will look something like

    SCOPE([Date].<<Hierarchy Name>>.ALLMEMBERS, [Measures].[Head Count]);
    This= IIF(IsLeaf([Date].<<Hierarchy Name>>.CurrentMember), [Measures].[Head Count], Avg(EXISTING [Date].<<Hierachy Name>>.<<Leaf Level>>.MEMBERS, [Measures].[Head Count]);


    -Remember to mark as helpful/the answer if you agree with the post.

    • Proposed as answer by Lola Wang Friday, May 18, 2012 6:42 AM
    Friday, May 18, 2012 3:53 AM