Measure to return top ranked item

[WesleyP8]

Hi all,

I'm trying to write a measure to return the highest ranked item, based on counts of rows. This seems like it should be straightforward but I'm struggling.

Here's the formula I'm using to produce the rank

Category Rank = RANKX(ALL(Data[Category]),[CountOfCategory])

Category	Count	Rank
Operations	234	2
Resources	145	3
Leadership	981	1

I want to create a few measures that return me the category for each rank i.e.

Rank 1 category = "Leadership"

Rank 2 category = "Operations"

Rank 3 category = "Resources"

I've tried using this formula:

Rank 1 category = CALCULATE(SELECTEDVALUE(Data[Category],"error"),[Category Rank]=1)

However, I get the error "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Any help would be much appreciated!

Thanks,

Wesley

[Greg_Deckler · Accepted solution]

See this post, it has both RANKX and non-RANKX ways of doing what you want.

To *Bleep* with RANKX - https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/td-p/1042520

It's probably how you are feeling right now...

[parry2k]

@WesleyP8 try this measure

Rank 1 category = CALCULATE(SELECTEDVALUE(Data[Category],"error"),
FILTER ( VALUES ( Data[Category] ), [Category Rank]=1)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!