MDX hierarchy level AVG

1
votes

i have a dimension hierarchy:

B - C - D - E

and one measure:

measures.eur

If i make calculated member: 

AVG([hierarchy].[All],[Measures].[eur])

i can get single average from all hierarchy members in the top level of hierarchy. But i want to have different average calculated from every level of the hierarchy.

So that when selecting hierarchy member C it shows the AVG of all the C's in the hierarchy, when selecting B it's AVG of all B's and so forth..

I tried:

 AVG(
 [HIERARCHY].PARENT,
[Measures].[eur]      
)

But it's not giving the average for all the members on the hierarchy levels, but it's very close to what i want.

1
ssasmdx

1 Answers

2
votes

You can try the following expression using the level function :

AVG ( [selected-member-of-c].level.members, [Measures].[eur] )