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

     

5 Replies

  • 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. 

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 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