topic Display value with highest average score in DAX Commands and Tips

Display value with highest average score

holywasabi — Sat, 18 Jan 2025 18:23:21 GMT

Hi all, newbie here. 😁

I want to show the "Wine Name" for the one that has the highest average score, and the 2nd highest average score.

With the condition that the "Wine Name" has appeared in the dataset more than once.

Note : Average score is calculated using wine_fact[points]

Link to pbix file :
https://drive.google.com/drive/folders/1d_6_pT5TJtg5KpRC79plvzXfako83upA?usp=sharing

Appreciate any help, much thanks! 😊

Re: Display value with highest average score

OwenAuger — Sun, 19 Jan 2025 08:28:05 GMT

Hi holywasabi

These days, I would recommend using the INDEX function to return a value with a given rank where the ranking is based on multiple values. In this case, it appears that you need Wine Name to be ranked first based on [score (average)] then based on Wine Name itself (ascending lexicographic ordering).

Here is how you could write the measures. The only difference between them is the WineRank variable which should be set to the desired rank.

wine name (wine score (average) top-1) = VAR WineRank = 1 -- Change to desired rank VAR WineScore = ADDCOLUMNS ( FILTER ( VALUES ( wine_fact[Wine Name] ), CALCULATE ( COUNTROWS ( wine_fact ) ) > 1 ), "@Score", [score (average)] ) VAR WineResult = SELECTCOLUMNS ( INDEX ( WineRank, WineScore, -- For equal scores, ties are broken using Wine Name (ascending lexicographic ordering) ORDERBY ( [@Score], DESC, wine_fact[Wine Name], ASC ) ), "@WineResult", wine_fact[Wine Name] ) RETURN WineResult

wine name (wine score (average) top-2) = VAR WineRank = 2 -- Change to desired rank VAR WineScore = ADDCOLUMNS ( FILTER ( VALUES ( wine_fact[Wine Name] ), CALCULATE ( COUNTROWS ( wine_fact ) ) > 1 ), "@Score", [score (average)] ) VAR WineResult = SELECTCOLUMNS ( INDEX ( WineRank, WineScore, -- For equal scores, ties are broken using Wine Name (ascending lexicographic ordering) ORDERBY ( [@Score], DESC, wine_fact[Wine Name], ASC ) ), "@WineResult", wine_fact[Wine Name] ) RETURN WineResult

Does this work for you?

Re: Display value with highest average score

Anonymous — Mon, 20 Jan 2025 09:28:37 GMT

Hi holywasabi

I wish you all the best. It seems to me that OwenAuger 's approach is the right one and gets you the desired results.

I would like to confirm whether you have successfully resolved this issue or if you need further assistance. If OwenAuger 's reply was helpful to you, could you consider marking it as a solution? This will help more users who are facing the same or similar difficulties. Thank you!

Best Regards,
Yulia Xu

Re: Display value with highest average score

holywasabi — Fri, 24 Jan 2025 07:30:25 GMT

Thank you so so much!

That is such an elegant solution, works as I had intended, and I learned something new. 😊

Thousand apologies for the late reply, was totally flooded with work (this is a side project). 😣

Re: Display value with highest average score

holywasabi — Fri, 24 Jan 2025 07:32:44 GMT

Thank you for offering more assistance, Yulia 🙂

It works like a charm ✨