0

In Excel I can use this formula to calculate the average in a certain period and ignore the top 20% most extreme values: TRIMMEAN(AY13:BH13,20%)

I cannot find any result when searching on MDX TRIMMEAN. I think I have to do something with RANK and calculate the average by ignore X from the top and the bottom of the ranked result. Does somebody have an example of something like that?

Ravi Ranjan
  • 740
  • 2
  • 10
  • 31
Kees Netelvrees
  • 119
  • 1
  • 8

1 Answers1

1

You need to use BottomPercent. From Microsoft's documentation, if you want to find the measure value in the bottom 80% (exclude the top 20%), then you can have something like this:

SELECT [Measures].[Reseller Sales Amount] ON 0,  
BottomPercent  
   ({[Geography].[Geography].[City].Members}  
   , 80  
   , [Measures].[Reseller Sales Amount]  
   ) ON 1  
FROM [Adventure Works]  
WHERE([Product].[Product Categories].[Bikes])

Further details can be found here BottomPercent

BICube
  • 4,451
  • 1
  • 23
  • 44
  • Thanks. TRIMMEAN cuts from both the top and the bottom. So if I want the middle 80%, I should first do BottomPercent with 90 and TopPercent with 89? – Kees Netelvrees Aug 07 '17 at 09:22