Hi there,

I guess it's a basic problem, but i wasn't able to find any answer here ...

I'm looking for a way to calculate averages and % of a measure based on the
upper level of a considerated dimension, ie :

Dimension -> Group Level -> Row Level

Group 1
Row 1
Row 2
Row 3
Group 2
Row 4
Row 5
Row 6

Dim -> Level 1 -> Level 2 -> Amount -> %
Dim -> Level 1 -> Total Amount -> Average

Dim -> Group 1 -> Row 1 -> 200 -> 20%
Dim -> Group 1 -> Row 2 -> 300 -> 30%
Dim -> Group 1 -> Row 3 -> 500 -> 50%
Dim -> Group 1 -> 1000 -> 333,33

Dim -> Group 2 -> Row 3 -> 200 -> 10%
Dim -> Group 2 -> Row 3 -> 400 -> 20%
Dim -> Group 2 -> Row 3 -> 1400 -> 70%
Dim -> Group 2 -> 2000 -> 666,66

For the % calculation, i think i could do that with a simple calculated
measure, ie : sum(current.parent.childs) / current.parent.childs.count. But
whatever i tried failed
For the average calculation, i found out some methods based on Custom Rollup
definitions (AS 2000), but no equivalent with SSAS 2005 ...

I'm not confident in writing MDX requests, and AS & SSAS are definitely too
different, any help with this would be greatly appreciated !

And please, excuse my poor english :-/
--
-= Tonio =-

Ok, here's a MDX statement that do the job for the average calculation :

IIF
(
count([Dim].[Hierarchy].CurrentMember.children) > 0,
sum([amount]) / count([Dim].[Hierarchy].CurrentMember.children),
sum([amount])
)

dunno if it's efficient, but it works ...

let's rock !
--
-= Tonio =-

And here's the statement for the % calculation :

sum([amount]) / sum(Ancestor([Dim].[Hierarchy].CurrentMember, 1), [amount])

i guess i should, at least, read some documentation about MDX ... it could
be helpful

--
-= Tonio =-