AVERAGE 3 HIGHEST

[stericontrol]

Hello!
I need to find and select the 3 highest values of each row (amount of products per month) from the table and calculate the average.
How could I do this by creating a measure using DAX?

[VahidDM · Accepted solution]

Hi @stericontrol 

It'd be better if you share a sample of your data here.

BTW, try this measure:

Top 3 Average = 
AVERAGEX (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[Product],
            'Table'[Value],
            "Rank",
                RANKX (
                    ALLEXCEPT ( 'Table', 'Table'[Product] ),
                    CALCULATE ( MAX ( 'Table'[Value] ) ),
                    ,
                    DESC,
                    DENSE
                )
        ),
        [Rank] <= 3
    ),
    [Value]
)

Output:

Download Link:https://gofile.io/d/4F5FfN

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos ✌️!!

[stericontrol]

Hello @VahidDM , thanks for you reply!

Your model looks great, that's exactly it! I tried to replicate in my base and there were some inconsistencies, I believe due to the complexity compared to your example.

I have data from 2016 so far, this was a complicating factor, they are also divided into other tables:

PBI_fSales - Fact Table - Sales

"Invoice ID"
"Product ID"
"Quantity of sold products"

PBI_dProdutos - Dimension Table - Products

"Product ID"
"Short Description (name)"
"Group"

BPI_dCalendar - Dimension Table - Dates

(Year, Month, day)

We have a ready-made measure that sums up all sales, called "Total Quantity".

I need to calculate the average of the TOP 3 products considering in context the year that is being filtered, your model calculates exactly as I need it, but I believe the date context when I tried to replicate in my database conflicted because the same product appears in years / months of other periods totaled and maybe this confuses the ranking

I'm new to PBI so forgive some nonsense

[MGaribay]

Hello Vahid!

What about if I do want to Avoid filters ?

I mean, if I do add an slicer and filter by product, to not affect average. 

[wdx223_Daniel]

NewMeasure=AVERAGEX(TOPN(3,ALL(DateTable[YearMonth]),[Total]),[Total])

[Jihwan_Kim]

One tip when removing filters of month name when calculating TOPN ( or RANKX): you also have to remove filters of the month-sorting column as well.

Link to the pbix file