How can I visualize the ranking of the region?

[Anonymous]

Hello all, I have trade data from the 17 regions. I want to create a measure that shows the place of the region based on trade values. For example, NY - 170 $, CA - 180 $, PA - 150$, and if I select PA it should show me 3rd place or 3/3, if NY 2nd place or 2/3 and finally CA 1st place or 1/3. How can I handle this?

[amitchandak]

@Anonymous , Try Rank and refer my blog given below

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

You may have to use differently - but check this out -https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814

[Fowmy]

@Anonymous 

Try this measure to rank.

RANK = 

RANKX(
    ALL('Table'),
    CALCULATE(SUM('Table'[VALUE])),,DESC
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

@Fowmy Thanks for your reply, it works fine. But I encountered one problem. With all regions, I have also Total for the counter inside in the region column. For example, NY - 150$, CA - 180$, PA-140$, ALL regions - 470 $ (150+180+140). And I set visual filter not to show ALL regions for all my pages in power bi reports. Currently, this:

RANK = RANKX( ALL('Table'), CALCULATE(SUM('Table'[VALUE])),,DESC )

 is showing me CA as 2nd place, NY as 3 and PA as 4th place because of ALL regions. How can I eliminate All regions when ranking, so I want to CA as 1st place. Thanks

[Fowmy]

@Anonymous 

Modified Measure:

RANK = 
IF( 
    SELECTEDVALUE('Table'[REGION]) = "ALL REGIONS",
    BLANK(),
    RANKX(
        FILTER(ALL('Table'),'Table'[REGION] <> "ALL REGIONS"),
        CALCULATE(SUM('Table'[VALUE])),,DESC
    )
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

@Fowmy  Now when All regions selected it is Blank, but if I select regions for all the measure is equal to 1.

Please note that I applied  Region except All regions filter for all pages using the visual filter = 

This means that ALL regions excluded from everywhere.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Please refer to the measure below:

RANK = 

RANKX(
    ALLSELECTED('Table'),
    CALCULATE(SUM('Table'[VALUE])),,DESC
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Fowmy]

@Anonymous 

Not quite what you are trying to do, either you can share the file or provide a clear explanation to understand further.

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn