cancel
Showing results for
Did you mean:
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
Super User IV

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

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

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

Proud to be a Super User!

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

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

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

Proud to be a Super User!

Regular Visitor

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.

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

Super User IV

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

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

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

Proud to be a Super User!

Super User II

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!

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

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

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

Proud to be a Super User!

Super User II

Yes, might do that .

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!

Announcements

#### Microsoft named a Leader in The Forrester Wave

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