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.

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.