cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stericontrol
Regular Visitor

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?

stericontrol_0-1630937796543.png

 

 

1 ACCEPTED SOLUTION
VahidDM
Resident Rockstar
Resident Rockstar

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:

VahidDM_0-1630987709059.png

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

 

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Picture1.png

 

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 

 

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

wdx223_Daniel
Super User
Super User

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

VahidDM
Resident Rockstar
Resident Rockstar

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:

VahidDM_0-1630987709059.png

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

 

View solution in original post

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

 

stericontrol_0-1631131799394.png

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!