locked
MDX version of GEOMEAN RRS feed

  • Question

  • Is there an MDX equivalent to the Excel GEOMEAN function?  What I want to do is write a DAX function in PowerPivot for GEOMEAN and figured if it was possible with MDX I could reverse engineer easily to DAX. 
    Chad-SWC
    • Moved by Raymond-Lee Monday, September 20, 2010 2:08 AM This should be a DAX question: DAX version of GEOMEAN (From:SQL Server Analysis Services)
    Wednesday, September 15, 2010 5:26 PM

Answers

All replies

  • One of ways to do so is to create an Assembly with Method called as "GetGoeMean" and pass the Period range and the Measure Name into the function. Inside the method, you can use the AMO object to get the measure value for the period ranges passed. Then you can use the C# logic as

    GM  = ( Sigma X(i)  1 to n) ^ 1/n

    Once the assembly is ready, add the assembly to OLAP and then use the function in a Calculated Measure.


    vinu
    Wednesday, September 15, 2010 5:58 PM
  • That would be a perfect approach if I was going the SSAS route.  I intend to use PowerPivot and DAX (Data Analysis Expressions).  So a custom assembly is a no go. 
    Chad-SWC
    Friday, September 17, 2010 7:30 PM
  • Hi Chad,

    A workaround is to use the alternative calculation: see http://en.wikipedia.org/wiki/Geometric_mean

    GM = Antilog\left[\frac1n\sum\ f logx\right]
    So you can write a DAX easily such as:

    power(10,(Sumx([Table],LOG10([Col]))/COUNTX([Table],[Col])))

    HTH,

    • Marked as answer by Chad - TSP Thursday, September 30, 2010 6:09 PM
    Friday, September 24, 2010 6:33 PM
  • That was an excellent answer.  The DAX sample you gave works perfectly.  Many thanks!
    Chad-SWC
    Thursday, September 30, 2010 6:10 PM