Forum Discussion
Replicating TRIMMEAN in DAX
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?
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!!
8 Replies
- Greg_Deckler
Community Champion
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.
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
Advocate I
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.
