Skip to main content
cancel
Showing results for 
Search instead 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

Reply
JbPa33
Helper I
Helper I

Rank with 2 columns

Hello,

 

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

JbPa33_0-1695991811367.png

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
lbendlin
Super User
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))

 

 

lbendlin_0-1696178965493.png

 

View solution in original post

1 REPLY 1
lbendlin
Super User
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))

 

 

lbendlin_0-1696178965493.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.