Forum Discussion

sy898661's avatar
sy898661
Helper V
4 years ago
Solved

Assigning different rank to identical values using RANKX

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 c...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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

sy898661's avatar
sy898661
Helper V
4 years ago

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