How to calculate Rank based on the Profit by City

[Anonymous]

Hi,

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

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

[Greg_Deckler]

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

[Zubair_Muhammad]

@Anonymous

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

[Anonymous]

Hi,

I have used your formula, but faing this issue:

What should I do now?

[v-lili6-msft]

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

[Anonymous]

I am facing the problem with using your Dax Formulla.

[Zubair_Muhammad]

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
)

.

[Anonymous]

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.

SO is possible here? Please help

[Zubair_Muhammad]

@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

[Anonymous]

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.

[v-lili6-msft]

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:

please check your data source and delete that  blank data.

Best Regards,

Lin