Average of Average

Hi guys,

i need to calculate the average of the average in a hierarchy

my hierarchy looks like this

1

1.1 Average: 3

1.1.1 Value: 1

1.1.2 Value: 5

1.1.3 Value: 1

1.1.4 Value: 5

1.1.5 Value: 1

1.1.6 Value: 5

1.2 Average: 5

1.2.1 Value: 5

1.2.2 Value: 5

now i would like to calculate the average of node 1 as avg(1.1, 1.2) what would be 4

on the 2nd level in can simply use

Avg([Item].[Item].currentmember.children, [Measures].[Value])

but how to calculate the average of that average?

the hierarchy i use is an attribute hierarchy with 5 levels and i need the calculation on each level

further i would need this caluclation for all my dimensions/hierarchies...

but i guess that wont be possible without far too much effort

any ideas how i could do this?

Here's an example of a recursive approach in Adventure Works, applied to the [Product Categories] hierarchy:

Code Snippet

With

Member [Measures].[AvgStockLevel] as

iif(IsLeaf([Product].[Product Categories].CurrentMember),

[Product].[Safety Stock Level].MemberValue,

Avg([Product].[Product Categories].Children,

[Measures].[AvgStockLevel])),

FORMAT_STRING = '#.0'

select

{[Measures].[AvgStockLevel]} on 0,

[Product].[Product Categories].Members on 1

from [Adventure Works]

|||

nice, thx

works perfectly!!

i just changed Avg([Product].[Product Categories].Children,

to Avg([Product].[Product Categories].CURRENTMEMBER.Children

so that it is easier to read (for me) - the result is the same

Code Snippet

With

Member [Measures].[AvgStockLevel] as

iif(IsLeaf([Product].[Product Categories].CurrentMember),

[Product].[Safety Stock Level].MemberValue,

Avg([Product].[Product Categories].CURRENTMEMBER.Children,

[Measures].[AvgStockLevel])),

FORMAT_STRING = '#.0'

select

{[Measures].[AvgStockLevel]} on 0,

[Product].[Product Categories].Members on 1

from [Adventure Works]

i didn't even know that it is possible to use recursive calculations within mdx

thx

Gerhard