Forum Discussion

stericontrol's avatar
stericontrol
Regular Visitor
5 years ago
Solved

AVERAGE 3 HIGHEST

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's avatar
    VahidDM
    5 years ago

    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 ✌️!!

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

 

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