Forum Discussion

cspress's avatar
cspress
Regular Visitor
8 years ago
Solved

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 ...
Need Help
cspress's avatar
cspress
Regular Visitor
8 years ago

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's avatar
Kmow
Icon for Advocate I rankAdvocate I
8 years ago

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-i-calculate-the-trimmean-formula-in-powerpivot-excel-2010-?forum=sqlkjpowerpivotforexcel

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago
        Very nice ImkeF ! Maybe you should do a blog series on Excel to M Translation?? 🙂
    • Kat_Finra's avatar
      Kat_Finra
      Frequent Visitor
      2 years ago

      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 😕