topic Re: Return TOPN when ties occur in a virtual calculated table in DAX Commands and Tips

Return TOPN when ties occur in a virtual calculated table

kristinamol — Wed, 21 Feb 2024 18:27:10 GMT

Hi community,

I have a dataset where I have product names, their avg. ranking and reviews volume. In my dashboard I want to show 3 KPI cards which need to return top 1, 2 & 3 ranked product based on Avg. Rating measure, with a condition that that product should have more than 5 reviews available (based on Reviews Volume measure).

I want to update this measure to account for a scenario where 2 products have the highest Avg. Rating and both satisfy the condition of Reviews Volume > 5 and return the brand with the highest Reviews Volume.

I want this KPI measure to return 1) top rated product based on [Avg. Rating] measure that satisfies [Reviews Volume]> 5 condition, and if there is a tie in Avg. Rating

2) return product with higher Reviews Volume.

so, in the example below I want Product B to be returned as Top 1 Product as it has Avg. Rating = 5 but also highest Reviews Volume:

Product	Avg. Rating	Reviews
Product A	5	1
Product B	5	226
Product C	5	2
Product D	5	3
Product E	5	2
Product F	5	7
Product G	5	3
Product H	4.86	84
Product I	4.85	27
Product J	4.83	78
Product K	4.8	7

However, my current measure doesn't account for it and hence returns Product F as Top Rated Product (probably because it's top 1 in alphabetical order). Can you please help me rework my measure? Here is is:

Top Rated Product KPI =

VAR TopProduct =

TOPN(

CALCULATETABLE(

FILTER(SUMMARIZE(

DIM_PRODUCT,

DIM_PRODUCT[PRODUCT],

"Avg. Rating", [Avg. Rating],

"Reviews Volume", [Reviews Volume]

), [Reviews Volume] > 5)

[Avg. Rating],

DESC

)

RETURN

MAXX(TopProduct, DIM_PRODUCT[PRODUCT])

Thanks!

Re: Return TOPN when ties occur in a virtual calculated table

Jihwan_Kim — Wed, 21 Feb 2024 19:20:10 GMT

Hi,

Please try something like below.

When showing top one based on two or more criteria, I suggesting try to use INDEX DAX function.

INDEX function (DAX) - DAX | Microsoft Learn

Top Rated Product KPI: = CALCULATE ( MAX ( DIM_PRODUCT[Product] ), KEEPFILTERS ( INDEX ( 1, FILTER ( ALL ( DIM_PRODUCT[Product] ), [Reviews Volume:] > 5 ), ORDERBY ( [Avg. Rating:], DESC, [Reviews Volume:], DESC ) ) ) )

Re: Return TOPN when ties occur in a virtual calculated table

kristinamol — Thu, 22 Feb 2024 14:51:23 GMT

Thank you, it seems to be working! Can you please recommend how I would need to update/rework my Top #2 Ranked Product measure? I understand I would need to update the first part based on the logic you shared, however, it doesn't work with that part of my measure that is highlighted in amber colour

Top Rated Product KPI (#2) =

VAR top_first = [Top Rated Product KPI]

VAR TopProducts =

TOPN(

CALCULATETABLE(

FILTER(SUMMARIZE(

DIM_PRODUCT,

DIM_PRODUCT[PRODUCT],

"Avg. Rating", [Avg. Rating],

"Reviews Volume", [Reviews Volume]

), [Reviews Volume] > 5)

[Avg. Rating],

DESC

)

VAR Flipped =

TOPN(

FILTER(TopProducts, DIM_PRODUCT[PRODUCT] <> top_first),

[Avg. Rating],

ASC

)

RETURN

MAXX(Flipped, DIM_PRODUCT[PRODUCT])

Appreciate your help!

Re: Return TOPN when ties occur in a virtual calculated table

Jihwan_Kim — Thu, 22 Feb 2024 16:52:03 GMT

Hi,

I am not sure if I understood your question correctly, but please try something like below.

Top 2 Rated Product KPI: = CALCULATE ( MAX ( DIM_PRODUCT[Product] ), KEEPFILTERS ( INDEX ( 2, FILTER ( ALL ( DIM_PRODUCT[Product] ), [Reviews Volume:] > 5 ), ORDERBY ( [Avg. Rating:], DESC, [Reviews Volume:], DESC ) ) ) )

Re: Return TOPN when ties occur in a virtual calculated table

kristinamol — Sat, 24 Feb 2024 14:39:16 GMT

Thank you again for your help - upon testing both Top 1 and Top 2 Rated Product KPI measures I discovered there are some instances when they are not working as intended. I'll give 2 examples:

Example #1

This is my current full table of top rated products with reviews volume > 5, sorted by descending Avg Rating

Product	Avg. Rating	Reviews
Product A	4.75	24
Product B	4.67	12
Product C	4.19	16
Other Products

So, I want my Top Rated Product KPI to return product A, Top #2 Rated Product KPI to return product B, Top #3 Rated Product KPI to return product C (for top #3 measure I used the same logic as you suggested for #2 product).

As a result, Top #1 and #3 KPIs return relevant products, while Top #2 returns Blank, I imagine this might be because its Reviews volume is lower than for Product C.

Example #2

This is another example of the full table of top rated products with reviews volume > 5 sorted by desc Avg Rating for a different market

Product	Avg. Rating	Reviews
Product A	4.17	8
Product B	3.68	19
Product C	3.63	9
Other Products

As such, Top #1 KPI returns Blank, Top #2 KPI returns Product B (correct), Top #3 KPI returns Product C (correct).

Do you know why the measures are not working as intended and if so, how would they need to be changed?

Thanks!

Re: Return TOPN when ties occur in a virtual calculated table

kristinamol — Mon, 26 Feb 2024 13:18:23 GMT

Hi, I was wondering if you had any thoughts on the issue with the measure you suggested? Thanks!

Re: Return TOPN when ties occur in a virtual calculated table

Jihwan_Kim — Mon, 26 Feb 2024 17:49:35 GMT

Hi,

Please provide the sample pbix file, and then I can try to look into it.

Thank you.