Rank with 2 columns

[JbPa33]

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 !

[lbendlin · Accepted solution]

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))