Forum Discussion

JbPa33's avatar
JbPa33
Icon for Helper I rankHelper 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 hav...
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))

     

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper 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))