TRIMMEAN

When you think you pick an easy one...

In my recent quest to create or catalog as many DAX equivalents for Excel functions, I figured this one would be a cinch. Well, not so much. Between poor documentation and vexing issues with DAX not having any kind of inherent sort for data, I very nearly pulled my hair out over this one and at times felt very much like the young woman in the photo. Well, anyway, a double, concurrent while loop and several burnt out, overloaded brain cells later, I was apparently able to solve a 2 1/2 year old request and I guess I was correct back then, it would require RANKX but that was only just the beginning! So cspress , here is your TRIMMEAN. Apologies for the delay...

TRIMMEAN = 
    VAR __Table = 
        ADDCOLUMNS(
            'Table',
            "Rank",RANKX('Table',[Value])
        )
    VAR __Percent = .2
    VAR __Count = COUNTROWS(__Table)
    VAR __Trim = MROUND(__Count * __Percent,2) / 2
    VAR __MaxRank = MAXX(__Table,[Rank])
    VAR __MinRank = MINX(__Table,[Rank])
    VAR __RanksTable =
        ADDCOLUMNS(
            ADDCOLUMNS(
                GROUPBY(
                    __Table,
                    [Rank],
                    "Count",COUNTX(CURRENTGROUP(),[Value]),
                    "Value",MAXX(CURRENTGROUP(),[Value])
                ),
                "CumulativeBottomCount",COUNTROWS(FILTER(__Table,[Rank] >= EARLIER([Rank]))),
                "CumulativeTopCount",COUNTROWS(FILTER(__Table,[Rank] <= EARLIER([Rank])))
            ),
            "BottomWhile",__Trim - [CumulativeBottomCount],
            "TopWhile",__Trim - [CumulativeTopCount]
        )
    VAR __MinBottom = MAXX(FILTER(__RanksTable,[BottomWhile]<=0),[BottomWhile])
    VAR __MinTop = MAXX(FILTER(__RanksTable,[TopWhile]<=0),[TopWhile])
    VAR __FinalBottomRankTable = 
        ADDCOLUMNS(
            FILTER(__RanksTable,[BottomWhile]>=__MinBottom),
            "Product",IF([BottomWhile]>=0,[Count]*[Value],([Count] + [BottomWhile]) * [Value])
        )
    VAR __FinalTopRankTable =
        ADDCOLUMNS(
            FILTER(__RanksTable,[TopWhile]>=__MinTop),
            "Product",IF([TopWhile]>=0,[Count]*[Value],([Count] + [TopWhile]) * [Value])
        )
    VAR __Bottom = SUMX(__FinalBottomRankTable,[Product])
    VAR __Top = SUMX(__FinalTopRankTable,[Product])
RETURN
    DIVIDE(
        SUMX(__Table,[Value]) - __Bottom - __Top,
        __Count - 2 * __Trim
    )

To clarify what is going on here, TRIMMEAN in Excel essentially ranks your data and trims off a number of rows equal to the percentage specified. The documentation doesn't really tell you about the ranking part, but it does it, it is not just trimming off the ordered list of rows. So, per the documentation, TRIMMEAN rounds the number of excluded data points down to the nearest multiple of 2. If percent = 0.1, 10 percent of 30 data points equals 3 points. For symmetry, TRIMMEAN excludes a single value from the top and bottom of the data set. Great. Where you run into trouble is when you have ties at the top and bottom of your dataset. Excel's TRIMMEAN is smart enough to only trim off the correct number of rows. So if you are trimming 3 points off the top and the bottom and have 2 1's and 3 2's, Excel's TRIMMEAN will only trim off the 2 1's and a single 2. Emulating this in DAX is not straight-forward at all and requires a lot of table gymnastics, double concurrent while loops and so on, such as me lying down on my bed with my eyes shut trying to figure out how to solve this problem until I got enough of an idea to drag myself back to my computer and continuing working on it. If you are wondering, the idea was around taking the averages of the tops and bottoms and multiplying that by the number of items to trim off from each side. Didn't actually work at all, but it eventually led me to the above solution, which I *think* works for all cases.

eyJrIjoiMmUyZjEzNDgtMWNhNC00OGI0LWE2ZDktNjA2ZmY1ZGVkMDdiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9