Forum Discussion

Caldowd98's avatar
Caldowd98
Icon for Helper I rankHelper I
4 years ago
Solved

Ranking based on variable

Hi community,   I have a table like the below, with cutstomers and sales quantity. Im looking to rank these customers based on the postcode area they sit within.   Any ideas how i can do this wit...
Need Help
Tips and Tricks
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Caldowd98 

    If you have multiple lines per customer, you can try this,

    create the measure below,

    RANK = 
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Postcode] = MIN ( 'Table'[Postcode] ) ),
    CALCULATE (
        SUM ( 'Table'[Sales Quantity] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer], 'Table'[Postcode] )
    ),
    ,
    DESC,
    DENSE
)

    result

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

You could add a calculated column like

Ranking =
var currentPostcode = 'Table'[Postcode]
var currentAmount = 'Table'[Sales]
return RANKX( FILTER( 'Table', 'Table'[Postcode] = currentPostcode), 'Table'[Sales], currentAmount)
  • Caldowd98's avatar
    Caldowd98
    Icon for Helper I rankHelper I
    4 years ago

    Hi great, thank you for help !

     

    I have however multiple lines per customer, how would i group these and sum the QTY ?

     

    Many thanks

    • v-xiaotang's avatar
      v-xiaotang
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Caldowd98 

      If you have multiple lines per customer, you can try this,

      create the measure below,

      RANK = 
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Postcode] = MIN ( 'Table'[Postcode] ) ),
    CALCULATE (
        SUM ( 'Table'[Sales Quantity] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer], 'Table'[Postcode] )
    ),
    ,
    DESC,
    DENSE
)

      result

       

      Best Regards,

      Community Support Team _Tang

      If this post helps, please consider Accept it as the solution to help the other members find it more quickly.