DAX Ranking Granularity

[mjmar025]

Hello,

I am looking to create a rankx measure that accomplishes the following:

I have a table with IDs, and some of the IDs are repeated if they are present in more than one location. I need a ranking that will be assigned to each of the rows for a specific ID based on the sum(Amount) for that ID compared to the sum(Amount) for all the other IDs. The image contains an example. The column titled 'Rank' is what I am getting with my current measure (below), but what I'd like is what's in the 'Desired Rank' column.

Is this possible?

Current Measure:

Rank = RANKX(ALLSELECTED('Table'), CALCULATE(SUM('Table'[Amount])))

[v-yulgu-msft · Accepted solution]

Hi @mjmar025 ,

Please refer to this measure.

rank =
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
    ,
    DESC,
    DENSE
)

Best regards,

Yuliana Gu