Hi

I have build a cube with avarage values on measure, but I need on the Total
cell of dimension the sum of each single average value
like example ex1.

ex1. C
C1 C2 C3 C4 C5 Total
R1 1 3 4 2 1 11
R2 5 2 4 3 6 20


I try to do this with the following steps:
1) create a dimension "C" with level C1-C5
2) create a measure "m_R1" and "m_R2" that sum the values of columns R1 e
R2 from table T (R1 or R2 columns can contain a 0 value)
3) create measures "m_R1_rows" and "m_R2_rows" that sum a colums R1_ROW and
R2_ROW from table T ( these columns contains a value 1 when the correspond
columns R1 o R2 containt a value greater of 0)
4) create calculate members "R1" and "R2" with this formula Iif(
[Measures].[m_R1_rows] = 0, Null, [Measures].[m_R1] /
[Measures].[m_R1_rows])

This work fine for each level, but on Total of dimension I have a average
value of rows R1 and R2 (see ex2 example)

ex2. C
C1 C2 C3 C4 C5 Total
R1 1 3 4 2 1 2
R2 5 2 4 3 6 4


Is possible for Total dimension have a sum of each value of level like ex1?
If yes how..?

Thanks in advance.

Simon.

How about if you define [R1] as follows:

'Iif(IsLeaf(Descendants([C].CurrentMember),
Iif([Measures].[m_R1_rows] = 0, Null,
[Measures].[m_R1] / [Measures].[m_R1_rows]),
Sum(Descendants([C].CurrentMember,,LEAVES),
[Measures].[R1]))'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!