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...
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,

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft 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
      Icon for Microsoft Employee rankMicrosoft 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,

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Great! This works.

        Thank you very much.