Assigning different rank to identical values using RANKX

[sy898661]

Hi all!

I have found similar questions regarding this issue but not exactly what I need, so posting in hopes someone can help. I have a table that looks like this:

I am using this to create a pareto, and since there are some rows that have identical # Delays, the RANKX function is grouping them together (which messes with the % line in the pareto chart). The other questions simiar to this all have different, but close, numbers in the second column, and mine are exact. Here are my functions:

 

Column: 

Delay Count = IF(not ISBLANK('Query'[DELAY_CODE]), 1, BLANK())

Measures: 

Delay Sum = SUM('Query'[Delay Count])

Delay Rank = IF(HASONEVALUE('Query'[DELAY_CODE]),RANKX(ALL('Query'[DELAY_CODE]),[Delay Sum],,DESC), BLANK())

Delay Cumulative Tot = IF(HASONEVALUE('Query'[DELAY_CODE]),SUMX(TOPN([Delay Rank], ALL('Query'[DELAY_CODE]),[Delay Sum],DESC), [Delay Sum]), BLANK())

Delay Total All = SUMX(ALL('Query'[DELAY_CODE]), [Delay Sum])

Delay Cumulative % = DIVIDE([Delay Cumulative Tot], [Delay Total All])

Is there a way to rank them differently?

Thank you so much in advance!

[v-luwang-msft · Accepted solution]

Hi @sy898661 ，

You could create the following column:

Delay_code1 = 
RANKX (
    FILTER (
        'Query',
        EARLIER ('Query'[Delay_code]) <> 'Query'[Delay_code]
    ),
    'Query'[Delay_code],
    ,
    DESC
)

rankfact = RANKX(Query,Query[#Delays]+0.00001*Query[Delay_code1],,DESC,Dense)

sum = CALCULATE(SUM(Query[#Delays]),FILTER(ALL(Query),Query[rankfact]<=EARLIER(Query[rankfact])))

sum = CALCULATE(SUM(Query[#Delays]),FILTER(ALL(Query),Query[rankfact]<=EARLIER(Query[rankfact])))

Final output:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[mahoneypat]

You will need to use a two-column rank to avoid that. Please see this article.

RANKX on multiple columns with DAX and Power BI - SQLBI

Pat

[sy898661]

Hi Pat, thank you for commenting!

I tried all of the codes in that article, and it still won't rank correctly, it is just returning 1 for everything

...

I made the following changes, and now the Rank works correctly (no repeating ranks), but Cumulative Total is not working as you would expect. it is still grouping together rows that have the same Sum #

added column:

Delay Index = 
RANKX (
    FILTER (
        'Query',
        EARLIER ('Query'[DELAY_CODE_1]) <> 'Query'[DELAY_CODE_1]
    ),
    'Query'[DELAY_CODE_1],
    ,
    ASC
)

added measure:

Delay Sum Index = SUM('Query'[Delay Index])

changed Delay Rank measure to:

Delay Rank = RANKX(ALL('Query'[DELAY_CODE_1]),[Delay Sum Index],,DESC)

Issues:

even though the rank is now different, its still grouping together the rows that have equal sums