Measure for top rank category

[PowerBIuser888]

Hello, 

I want to add some text in my dashboard such as "XXX had the highest score" where XXX updates depending on how the dashboard is filtered. 

I have played around with ranking/if statements but can't work out how to get a measure to work. In simple terms what I am trying to achieve is: IF([RANK]=1,[category])

Is this possible? Any help would be great - thank you!

[goncalogeraldes]

Hello there @PowerBIuser888 ! Have you tried using the "Smart Narrative" visual or even the "Q&A"? Both can greatly help you achieving your goal 🙂

Nevertheless, you can try this:

Top Rank =
CALCULATE (
    SELECTEDVALUE ( Products[ProductCategory] ),
    TOPN (
        1,
        SUMMARIZE (
            Products,
            Products[ProductCategory],
            "score", Your formula for ranking system
        ),
        [score], ASC
    )
)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[PowerBIuser888]

Hi @goncalogeraldes , 

Thank you - will give this a try now (both Smart Narrative and your formula and will let you know how I get on). 

Thanks! 

[PaulDBrown]

Do you already have the measure for the rank? If so, you can use:

Rank 1 Category = 
CALCULATE(MAX(Table[Category]), FILTER(VALUES(Table[Category]), [Rank] = 1))

[PowerBIuser888]

Hi, 

Yes I do - I have some -0's in my data so used the following measure to account for minus numbers... 

RANK=IF(

NOT(ISBLANK([change yoy])),

RANKX(

FILTER(ALLSELECTED(Combined[Question]), NOT( ISBLANK([change yoy]))),

[change yoy]

)

)

Thanks"

[PaulDBrown]

Try the measure I posted above. Beware that this will only return the max value if there are more than one categories ranked as 1. To avoid this, you can try

Rank 1 Category =
CONCATENATEX (
    FILTER ( VALUES ( Table[Category] ), [Rank] = 1 ),
    Table[Category],
    ", "
)

[PowerBIuser888]

Thank you - will give this a go and let you know how I get on.