cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to calculate Rank based on the Profit by City

Hi,

I have fileds [City] and [Profit] and need to show rank like below based on the Profit.

Rank.PNG

So how can I create a measure based on the Profit ? Please help

10 REPLIES 10
Greg_Deckler
Super User
Super User

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@Anonymous

 

Measure =
RANKX (
    ALLSELECTED ( Table1[City] ),
    CALCULATE ( SUM ( Table1[Profit] ) ),
    ,
    DESC,
    DENSE
)

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hi,

I have used your formula, but faing this issue:

Rank2.PNG

 

What should I do now?

Hi@ dsandip59

When you use Rankx function ,You need to pay attention to the function format, you just miss ','  before 'Desc' in your formula.

Top cities with profit= (
    ALLSELECTED ( Table1[City] ),
    CALCULATE ( SUM ( Table1[Profit] ) ),
    ,
    DESC,
    DENSE
)

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Rank3.PNG

 

 

I am facing the problem with using your Dax Formulla.

Put one more comma before Desc

 

Please see the 2 commas in red font at the end of fourth line below

i.e.

 

Measure =
RANKX (
    ALLSELECTED ( Table1[City] ),
    CALCULATE ( SUM ( Table1[Profit] ) ), ,
    DESC,
    DENSE
)

.

 

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hi,

Your previous calculation is correct, so thank you very muh for this help.

But as I am using DENSE ranking can I arrange the city name based on ranking on profit i.e City with Highest profit will come first and then 2nd one.. current in the below screen shot it is not arranging the city name with ascending order on Rank measure like below:Rank4.PNG

 

So my requirement is:

1. For country Afganistan, Highest profitable city will come first with Rank 1, and then 2nd highest profitable city with rank 2 etc.

SO is possible here? Please help

 

@Anonymous

 

I believe Sorting by multiple columns is still not available

You can vote for this idea

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15038169-multiple-column-sort

 

But there can be a trick to do this.

If you can share your file, i will try to look into it

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hi,

 

I am sharing lonk to you with my sample application. Please help me.

https://drive.google.com/open?id=1XRrfN-InVS5mb7K1VuheQ3feXaejarDq

 

In the above app I want that when I select a country, or sort the table with country name, it will also show values with first rank at row 1, and then 2nd rank to row 2 etc.

menas sorting the country with Rank also from Rank 1 will be the first and then 2nd one etc.

Hi@dsandip59

After my test of your DEMO2 , i find that there's a line of blank data and it may be the reason that leads your wrong result as below:

2.PNG1.PNG

 

please check your data source and delete that  blank data.
 
Best Regards,
Lin
Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors