Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Ranking in a column

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.

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

4 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        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,