topic Re: AVERAGE 3 HIGHEST in DAX Commands and Tips

AVERAGE 3 HIGHEST

stericontrol — Mon, 06 Sep 2021 14:18:10 GMT

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?

Re: AVERAGE 3 HIGHEST

VahidDM — Tue, 07 Sep 2021 04:11:24 GMT

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

Re: AVERAGE 3 HIGHEST

wdx223_Daniel — Tue, 07 Sep 2021 04:40:23 GMT

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

Re: AVERAGE 3 HIGHEST

Jihwan_Kim — Tue, 07 Sep 2021 07:50:12 GMT

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

Re: AVERAGE 3 HIGHEST

stericontrol — Wed, 08 Sep 2021 20:13:17 GMT

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

Re: AVERAGE 3 HIGHEST

MGaribay — Thu, 07 Jul 2022 16:01:10 GMT

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.