cancel
Showing results for
Did you mean:
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.

10 REPLIES 10
Super User

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Community Champion

@Anonymous

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

Regards
Zubair

Anonymous
Not applicable

Hi,

I have used your formula, but faing this issue:

What should I do now?

Community Support

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

I am facing the problem with using your Dax Formulla.

Community Champion

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

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:

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.

Community Champion

@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

Anonymous
Not applicable

Hi,

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.

Community Support

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:

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.

Announcements