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
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?? 🙂
      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Yes, might do that .

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

  • 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 😕