Replicating TRIMMEAN in DAX

[cspress]

Hi - I currently run a Pivot table that shows values aggregated by day (columns) and category (rows). I then use the TRIMMEAN function in Excel across the rows (days) to strip out the top and bottom 20% of values (the outliers) and calculate the average of the rest. This gives me a single value for each row (category) which I then refer to in power BI to generate a treemap.

I'd like to skip the need to split out the days and apply the TRIMMEAN function to them. However DAX doesn't appear to have this functionality built in. I'm trying to replicate it in DAX without much success.

I've created the following measures:

Value Sum = sum(Database[Test Value])

Max day value =maxx(values(Database[Day of Test]),Database[Value Sum])

Min day value =minx(values(Database[Day of Test]),Database[Value Sum])

All those work fine. However when I try to combine them into a formula that calculates an average of values excluding the outliers, I just get blanks. It works fine if I hard code a boundary, but not if I try to pull one in from the database.

For simplicity the formula below just has the lower bound (minimum value + 20%). Any ideas what's not working?

=
AVERAGEX (
FILTER (
VALUES ( Database[Day of Test] ),
Database[Value Sum]
> Database[Min Day Value] * 1.2
),
Waterfalls[Response Sum]
)

I guess the filter context is preventing the [Min Day Value] measure from calculating properly, but I've tried wrapping it into a Calculate without success.

Any ideas?

[Greg_Deckler]

Haven't coded this, but a technique that I would think would work would be to implement a RANK of your values. Then, you could write a DAX formula that would FILTER out your top and bottom percentage of rows and then do your calculation on what remains.

[cspress]

Thanks! In fact your answer made me realise that I was misunderstanding how TRIMMEAN works, I thought it was excluding all results that sit outside a certain range, whereas it's just excluding a percentage of the results. That hopefully makes things simpler. I'll try with RANK and see how I get on. I'll probably be posting a follow up question!

[Kmow]

Since this post is one of the top results when searching for "TRIMMEAN in DAX" ... and hasn't yet been resolved, I wanted to point out this blog post that helps you get RANKX() to keep the filter context. I believe this is necessary to get a relative TRIMMEAN in DAX working. I strugged with this for awhile, but this blog post helps a lot.

https://ayadshammout.com/2013/02/19/dax-rankx-function-scenarios/

Once the RANKX is working (it was odd me at first that it is a measure, but if you test the measure using a pivot table, you can verify that yours is working).... I then plan to roughly follow the methodology in the next link. I'm not actually sure if it will get the job done. I'll try and report back.

https://social.msdn.microsoft.com/Forums/vstudio/en-US/e193338e-dbbe-462b-99d6-1bd26a033227/how-can-...

[Greg_Deckler · Accepted solution]

@cspress , @Kmow 

So, for the record, I actually solved this finally.

https://community.powerbi.com/t5/Quick-Measures-Gallery/TRIMMEAN/m-p/1074075

I stumbled across this old thread when posting that Quick Measure! 🙂

@ImkeF - You might like this as I believe it resolves the issues with ties, which is what drove me nuts trying to solve!!

[Kat_Finra]

Hi Greg, Thank yu for posting this! I have a table with repeating values at the top and bottom that PBI might be droping  out of the set. When I compate the 10% TRIMMEAN from exce to PBI version I get different results 😕 

[ImkeF]

Hats off on that one @Greg_Deckler !

Just to make you jealous how easy it would have been on the bright side 😉 :

// TRIMMEAN on the bright side :)
let
    Source = Table.Buffer( Table.Sort(Array,{{"Value", Order.Ascending}}) ),
    CountOfTotalRows = Table.RowCount( Source ),
    CutOffAtEachSide = Number.RoundDown((percentage * CountOfTotalRows) / 2),
    RelevantRange = Table.Range( Source, CutOffAtEachSide, CountOfTotalRows - CutOffAtEachSide * 2),
    Result = List.Average( RelevantRange [Value] )
in
    Result

or the functionized version that accepts the same arguments as the Excel-function ( an array (list) and a percentage (scalar) :

(ListOfValues, Percentage) =>
let
    Source = List.Buffer( List.Sort(ListOfValues, Order.Ascending) ),
    CountOfTotalRows = List.Count( Source ),
    CutOffAtEachSide = Number.RoundDown((Percentage * CountOfTotalRows) / 2),
    RelevantRange = List.Range( Source, CutOffAtEachSide, CountOfTotalRows - CutOffAtEachSide * 2),
    Result = List.Average( RelevantRange)
in
    Result

[Greg_Deckler]

Very nice @ImkeF ! Maybe you should do a blog series on Excel to M Translation?? 🙂

[ImkeF]

Yes, might do that .

Have thought about this for a while actually and made some functions already over the time 🙂