Forum Discussion

Caldowd98's avatar
Caldowd98
Helper 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 within a Dax formula ? Many thanks !

 

CustomerPostcodeSales QuantityRank
ADY10002
BDY12001
CB15001
EB12502
FWR10001
GWR9502
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.

3 Replies

  • johnt75's avatar
    johnt75
    Super 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
      Helper 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
        Community 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.