Drop 3 highest and 3 lowest values from static 12 month average

[jhmonson]

I'm sharing a pbix file (attached)...

https://drive.google.com/file/d/1zXK30zA517fa1yAjkvqs0iJbtScLskqt/view?usp=sharing

Hello! I have a data set containing number of uses of an item by month for 12 months. I need to be able to pick an item from the slicer and have the table show usage in each month column 1, the average of the 12 months in every row (not rolling) as column 2 (I've done this in a measure calculating the average for the selected item with an ALL function), and the average with the top 3 and bottom three values dropped in column 3. The last part is where I'm getting stuck. Here's my visual so far: 

 

So I'm looking for one more column that would drop the highest and lowest three values from usage and average the remaining 6 for whatever item I have selected, eventually looking something like this if I've picked item 100008:

Any ideas? Thanks!

[CNENFRNL · Accepted solution]

Mid AVG = 
VAR __m = ALLSELECTED( 'Usage Data'[Month] )
VAR __rank = [RANK]
RETURN
    IF(
        __rank > 3 && __rank <= COUNTROWS( __m ) - 3,
        AVERAGEX(
            FILTER(
                __m,
                VAR __r = [RANK]
                RETURN
                    __r > 3
                        && __r <= COUNTROWS( __m ) - 3
            ),
            [Total]
        )
    )

Excel worksheet formula is way powerful to solve such a simple question,