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". The context is the first column (Business/Division)

The sort column is a calculation of two columns TRIFR LY and TRIFR YTD:

Sort Measure = ([TRIFR YTD] * 100) - DIVIDE(1, [TRIFR LY], 0)

This column is working fine. It's a trick I found on this forumn to sort by two columns.

 

However, the rankx() doesn't produce the desired result. I tried both "Dense" and "Skip" options for ties.

 

Here's the formula for the rank:

 

Sorting Rank = RANKX (ALL( DATA[Consolidation] ),
    [Sort Measure],,ASC
)

Please note there are zeros and there could be identical values in the "sort" column. Sorting of those is not important as long as they all follow the numbers below and above.

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

     

     

     

9 Replies

  • 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

    Update: I have changed the ranking measure to:

     

    Sorting Rank = RANKX (
    ALLSELECTED( DATA[Consolidation] ),
    CALCULATE([Sort Measure]),,ASC,Skip
)

    with a slight improvement but the rank gets reset to 1 when the values are 0. You can see that it's still not working. The measures involve use some time intelligence. Please let me know if you want me to post them.

     

    • themistoklis's avatar
      themistoklis
      Community Champion
      7 years ago

       

       

      atitarev

       

      How about this formula

       

      Rank = 
    RANKX(
        'DATA',
        'DATA'[Sort Measure],,
        ASC
        )