CODEWITHSUNDEEP
powerbidaxpowerbi-desktoprank
Gieses
Gieses

Reputation: 1

How can I change my Rankx formula to a formula where tied ranks are getting skipped?

I am getting frustrated with a rankx formula in my Power BI Dashboard. I hope someone can hep me with it.

Situation: I have a table "Events". This contains the results (column "Points") & event rank of Athletes for several events. I would like to come up with a overall ranking for these Athletes (all events combined). If there are Athletes which have the same sum of points, the Athlete who achived the better single event rank shall be ranked before the other. Therefore I came up with an "Tiebreaker" (Formula: Points - Event rank /100). Below you can see the table. Table_situation

Goal: Eventually, I want to come up with a table like the one below (with tied ranks skipped!). The last column is my desired ranking: desired ranking

My wrong Rankx tries:

First I tried to build this formula in a calculated column. However here I only achived to rank the correct ranking with dense. When changing the formula to Skip: RANKX(Events,Events[Tiebreaker],,DESC,Skip) the rankx skips too much as there are multiple rows per athlete.

Next I tried to build this formula as Measures.

First measure: Ranker_Sum = Sum(Events[Tiebreaker])

Second measure: Rank as a measure = Rankx(All(Events),Calculate(Sum(Events[Tiebreaker])),,DESC,Skip) Alternative: RANKX(ALLSELECTED(Events),[Ranker_Sum],,DESC,Dense)

Here I got this wrong result: my_measure_result

You can download the pbix file via the following link: (https://1drv.ms/u/s!AttG8pc8y9HJvF9APs6IXR2QWUnA?e=QMQNvM)

I have to do this ranking as a measure, correct? Which formula can I use? Thanks!!

Upvotes: 0

Views: 183

Answers (1)

Ashok Anumula
Ashok Anumula

Reputation: 1515

You can try

Rank as a measure = Rankx(All(Events),Ranker_Sum,,DESC,Skip)

Upvotes: 1

Related Questions