cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cspress
Regular Visitor

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?

1 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!!


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Greg_Deckler
Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!

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-...

@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!!


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yes, might do that .

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors