Ranking Based on Two Columns

[FaisalKarawalla]

Hello,

Am trying to come up with a logic that will help me in creating a ranking.

So the rank depends on two variable percentages (Columns). Lets Say Percentage 1 and Percentage 2.

The desirable situations for determining the rank are combination of:

1. Percentage 1 should be heighest.

2. Percentage 2 should be lowest.

Is there any formula which I can create a measure with, if not any suggested work arounds?

[v-binbinyu-msft · Accepted solution]

Hi @FaisalKarawalla ,

Please try below steps:
1.Create an auxiliary column

HP_Column =
VAR rank_by_kpi =
    RANKX ( 'Table', 'Table'[KPI Percentage],, DESC )
VAR rank_by_death =
    RANKX ( 'Table', 'Table'[Death Percentage],, ASC )
VAR ct_rows =
    COUNTROWS ( 'Table' ) + 1
RETURN
    rank_by_death + DIVIDE ( rank_by_kpi, ct_rows )

2.Rank by the new auxiliary column

Rank_Num =
RANKX ( 'Table', 'Table'[HP_Column],, ASC )

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FaisalKarawalla]

Thank you so much!

Works like a charm!

[daXtreme]

Hi there

Can you please show us (a picture?) an example of what you'd like to achive and what your input is? It'll be easier to come up with something useful. Thanks.

[FaisalKarawalla]

Thank you for the response, find the picture attached.

The end result is to get a list of the most signifincant hospitals on which if the KPI percentage is improved it will result in the most drastic change on the overall average KPI permorfance.