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

     

     

     

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi atitarev,

 

1. Is [Sort Measure] a measure or a column?

2. The image shows [Business/Division] while it's [Consolidation] in your measure. Maybe you can try this one.

 

Sorting Rank =
RANKX (
    ALL ( DATA[Business/Division] ),
    CALCULATE (
        SUM ( data[Sort Measure] ),
        ALLEXCEPT ( data, data[Business/Division] )
    ),
    ,
    ,
    DENSE
)

3. Can you create a dummy sample and share the download link here?

 

 

Best Regards,
Dale

  • 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.