Ranking based on variable

[Caldowd98]

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 !

Customer	Postcode	Sales Quantity	Rank
A	DY	1000	2
B	DY	1200	1
C	B	1500	1
E	B	1250	2
F	WR	1000	1
G	WR	950	2

[johnt75]

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]

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 · Accepted solution]

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.