Forum Discussion

holywasabi's avatar
holywasabi
Frequent Visitor
1 year ago
Solved

Display value with highest average score

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...
  • OwenAuger's avatar
    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
        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?