how to get geometric mean for a measure in cube in ssas 2012

Question

I have one measure called StayInHospital in my cube . I want to calculate the geometric mean for this measure . Please see the example

Day ,   AccoutNumber , Stayinhospital , VisitCount

1   ,        A1      ,       2         ,   1
1   ,        A2      ,       4         ,   1
1   ,        A3      ,       8         ,   1

Geometric Mean for above example for Day 1 for Stayinhospital measure = (2*4*8)^1/3

                                                           =4

I have done the following calculation in Calculated member

  Measures.WeightLog as log(StayInHospital)
  Measures.ProductOfWeights as 10^Measures.WeightLog
  Measures.GMLOS as (Measures.ProductOfWeights)^(1/(Measures.VisitCount))

I am not getting the expected result.

It is summing the StayInHospital Value instead of multiplying please help .

Answer 1

Calculated member formulas are applied after aggregation. Hence the best solution would be to calculate the logarithm already during data loading, e. g. as a named calculation in the Data Source View, e. g. add a calculation with the expression

log(StayInHospital) / log(10.0)

assuming your relational column is named StayInHospital. This expression takes care of the fact that Log is for base e, not for base 10, and converts it to a logarithm of base 10.

Then, define a normal summable measure based on this named calculation, named e. g. LogStayInHospital.

Finally, define your measure GMLOS as

( 10 ^ Measures.LogStayInHospital ) ^ (1 / Measures.VisitCount)

or, equivalently, as

10 ^ (Measures.LogStayInHospital / Measures.VisitCount)

If everything works fine, and you do not need it for debugging any more, you can make the measure LogStayInHospital invisible.