Forum Discussion

atitarev's avatar
atitarev
Helper I
7 years ago
Solved

Ranking issue

Hi Power BI experts,   I am having an issue in getting the RANKX() working correctly for me.   The ranking column is last, which is not working. It's based on the previous column "Sort". T...
Need Help
  • themistoklis's avatar
    themistoklis
    7 years ago

    atitarev

     

    Try the following formula:

     

    Sorting Rank = IF (
    HASONEVALUE ( DATA[Consolidation] ), 
    RANKX ( ALL ( DATA[Consolidation], DATA[Level Leader] ), [Sort Measure], , ASC, Dense)
)

    It shows this ranking

     

     

     

atitarev's avatar
atitarev
Helper I
7 years ago

v-jiascu-msft

 

[Sort Measure] is a measure

[Consoldiation] is the name of the column but [Business/Division] is the name used in the table.

I have obfuscated the data and saved a simplified version of the report:

 

I have shared the files on OneDrive:

PBIX

 

CSV

 

Please let me know if you can't access them.

I found that without the [Leader] column it works OK, as in a 2nd page but I couldn't remove it using ALLEXCEPT.

 

Your assistance is appreciated.

themistoklis's avatar
themistoklis
Community Champion
7 years ago

atitarev

 

Try the following formula:

 

Sorting Rank = IF (
    HASONEVALUE ( DATA[Consolidation] ), 
    RANKX ( ALL ( DATA[Consolidation], DATA[Level Leader] ), [Sort Measure], , ASC, Dense)
)

It shows this ranking

 

 

 

  • atitarev's avatar
    atitarev
    Helper I
    7 years ago

    Thank you, themistoklis! That's exactly what I need.