Forum Discussion

JbPa33's avatar
JbPa33
Helper I
2 years ago
Solved

Rank with 2 columns

Hello,

 

I would like to create a rank on Power BI with a condition. Here is my data :

I have the number of contracts for each partner for the years N-1 and N (both data are measures). I have calculated, with a measure, the growth rate and I want to add a measure "rank" according to the growth rate. But, if the growth rate is empty, i want calcul the rank according to the number of contracts N.

The result would be : 

Partner Rank

     A        1

     B        2

     C        3

     D        4

     E        5

 

Thank you for you help !

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    At some point you need to decide if that can be done with calculated columns or if it needs measures.  I prefer to propose measure based solutions. Having said that the Growth field is a column

    Growth = divide([N]-[N-1],abs([N-1]),0)

     

    Here's the measure that does the heavy lifting

     

    RankM = 
var mng = filter(ALLSELECTED('Partner contracts'),[Growth]=0)
var mngv = maxx(mng,[N])
return if(sum('Partner contracts'[Growth])=0,sum('Partner contracts'[N]),mngv+sum('Partner contracts'[Growth]))

     

    and the Rank measure then is boilerplate

     

    Rank = RANK(SKIP,ALLSELECTED('Partner contracts'),ORDERBY([RankM],DESC))

     

     

     

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    At some point you need to decide if that can be done with calculated columns or if it needs measures.  I prefer to propose measure based solutions. Having said that the Growth field is a column

    Growth = divide([N]-[N-1],abs([N-1]),0)

     

    Here's the measure that does the heavy lifting

     

    RankM = 
var mng = filter(ALLSELECTED('Partner contracts'),[Growth]=0)
var mngv = maxx(mng,[N])
return if(sum('Partner contracts'[Growth])=0,sum('Partner contracts'[N]),mngv+sum('Partner contracts'[Growth]))

     

    and the Rank measure then is boilerplate

     

    Rank = RANK(SKIP,ALLSELECTED('Partner contracts'),ORDERBY([RankM],DESC))