Ranking issue

[atitarev]

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.

[v-jiascu-msft]

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]

@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 · Accepted solution]

@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]

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

[atitarev]

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]

@atitarev

How about this formula

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

[atitarev]

Thank you, @themistoklis but I only get non-sensical numbers. I need to get the context right.

[themistoklis]

@atitarev

Any chance to share the workspace with us and also an image on how the final dataset will look like?

[atitarev]

@themistoklis, sorry, the data is sensitive and my account doesn't allow me to attach files yet, anyway.