cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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 !

1 ACCEPTED SOLUTION
Super User

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

Super User

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