Forum Discussion
Display value with highest average score
- 1 year ago
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 WineResultwine 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 WineResultDoes this work for you?
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
WineResultwine 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?
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). 😣