Forum Discussion

FaisalKarawalla's avatar
FaisalKarawalla
Frequent Visitor
4 years ago
Solved

Ranking Based on Two Columns

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?

 

measure
rank
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

4 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    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's avatar
      FaisalKarawalla
      Frequent Visitor
      4 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.