I have a employee-cube where each person can have more than one
employments. I have a distinct count measure for counting distinct
persons and an Age measure.
But how can I calculate the average age of employees? I can't just sum
the ages and divide by number of distinct persons since each person can
have more than employment. It wouldn't be correct to divide by number
of employments either.
E.g.

Person,Employment,Age
----------------------------------------
Person1,Employment1,20
Person1,Employment2, 20
Person2,Employment1, 80

The correct age average is 50 (100/2).
It would seem like simple enough, but I can't figure out how to solve
this in MDX

Any hints?

Thanks

Think I found a solution after all:

Aggragate function Max on the age measure

Then did the calculation Average age as:
Avg(NonEmptyCrossJoin([Person Unique].[Person
Unique].Members,{[Measures].[Distinct Persons]}),[Measures].[Maxage])

Seems to give the result I want, have to test it a bit further though.

/Magnus

Magnus skrev: