Sum of Calculation VS AVG of Results

i have simple measure table, called myfacts. in table have values 3 columns call x, y , z.

sample data follow:

my formula is: (x + y) / z

(55+66)/77 = 1.6

(44+33)/22 = 3.5

my current calculation (sum of x) + (sum of y) / (sum of z) = 2

however want is:

i want mdx product average of 1.6 , 3.5 equals 2.5

the current calculation follow:

with

member measures.a as

iif([measures].[z]<>0,(

([measures].[x]) +

([measures].[y])) /

([measures].[z),0)

select measures.a on 0

from

[mi dashboard dw]

what proper mdx calculate average of results each calculation?

ie result of 2.5 not 2.0

hi,

calculate row average in dsv named calculation or in data source table/view rowaverage = (x+y) / z

then in measure group create measure column sum aggregation.

make sure have count of rows measure (say [fact count])

then create calculated measure as measures.[rowaverage sum] / measures.[fact count]

hth,

hrvoje piasevoli

SQL Server > SQL Server Analysis Services

Lun