Ranking in a column

[Anonymous]

Hello All:I need help with a formula in a calculated column that would show rank of a manufacturer within a cost center. In the image above all lines in the column "Ranking" for manufacturer "ARM" and cost center "710" should show 1 since the manufacturer "ARM" is the number one in cost center "710". See below.

And so on - all lines in the table for manufacturer CNG and cost center 710 should display 2.

Thank you all in advance.

P.S. As you can see above, I did find solution for a measure, but I do not need a measure. I need to use calculated column.

[v-cherch-msft]

Hi @Anonymous 

You may try below formula.

Column =
RANKX (
    'Table',
    CALCULATE (
        SUM ( 'Table'[EXT COST] ),
        ALLEXCEPT ( 'Table', 'Table'[COST_CENTER], 'Table'[MANUFACTURER] )
    ),
    ,
    DESC,
    DENSE
)

Regards,

[Anonymous]

Hi there,

The formula above is close but not exactly what I am looking for. Basically for cost center 710 and manufacturer ARM it shows 1, for manufacturer CNG it shows 13. If I look for example at cost center 720, the largest manufacturer starts from rank 3, the next one is 4 and so on. Something is missing - the ranking is not reset at cost center level.

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

Please check below formula.

Column = 
RANKX (
    FILTER ( 'Table', 'Table'[COST_CENTER] = EARLIER ( 'Table'[COST_CENTER] ) ),
    CALCULATE (
        SUM ( 'Table'[EXT COST] ),
        ALLEXCEPT ( 'Table', 'Table'[COST_CENTER], 'Table'[MANUFACTURER] )
    ),
    ,
    DESC,
    DENSE
)

Regards,

[Anonymous]

Great! This works.

Thank you very much.