Give same rank to same group.

[archana_c]

I have a table visual with the following columns, participant name(group), allocation no, outstanding balance. I need to rank the participant name(group) based on outstanding balance (desc order), but the same rank to be given to the same participant name. for example, if the participant A has two allocation no, then both the rows should be ranked as 1(or same rank).


@amitchandak 

[Anonymous · Accepted solution]

Hi @archana_c ,

You can create a measure as below to get it, please find the details in the attachment.

rank = 
RANKX (
    ALLSELECTED ( 'Table'[participant name] ),
    CALCULATE (
        MAX ( 'Table'[outstanding balance] ),
        ALLEXCEPT ( 'Table', 'Table'[participant name] )
    ),
    ,
    DESC,
    DENSE
)

Best Regards

[archana_c]

Thank you for the solution. Its working fine.
Is it possible to remove rows with 0 values if there are no negative values,(need to keep the rows with 0 if there are negative values) Because i am using this measure inorder to get the top 5 list(as well as bottom 5). But if there are more than one participant with 0 value, its ranked the same and if the rank is below 5 then all are listed. Let me know if any condition can be added in the dax. 
Thank you