TRIMMEAN in MDX

Question

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?

Answer 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