Forum Discussion

stericontrol's avatar
stericontrol
Regular Visitor
4 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
    4 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 ✌️!!

     

VahidDM's avatar
VahidDM
Super User
4 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 ✌️!!

 

  • stericontrol's avatar
    stericontrol
    Regular Visitor
    4 years ago

    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's avatar
    MGaribay
    New Member
    4 years ago

    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.