cancel
Showing results for
Did you mean: 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? 1 ACCEPTED SOLUTION  Resident Rockstar

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: If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

4 REPLIES 4  Super User 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.

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  Super User

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

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: If this post helps, please consider accepting it as the solution to help the other members find it more quickly. Regular Visitor

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  