I have a fact table that lists each employee in the company on the last day
of the month. The dimensions are the day of the month and the employee. With
each record I bring in a value for the active and terminated employees of 0
or 1 for each. When I view this a month at a time, it gives me a count of how
many active employees I have at the end of the month and how many
terminations to that I can then calculate the turnover percentage. When I
roll it up into quarters and years, however, I want the activeemployee
measure to be an average of the individual month counts. How can this be done?

You could create a DistinctCount measure based on the Employee key (use
column binding) in the fact table and then filter the count by active..

( [Employee].[ActiveInactive].[1], [Measures].[Employee Count] ) /
[Measures].[Employee Count]

I'm assuming you also populate the rest of the month in the fact table with
NULLs so your measures don't get skewed.



"Bryan Wheeler" <BryanWheeler (AT) discussions (DOT) microsoft.com> wrote