Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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)
Hi great, thank you for help !
I have however multiple lines per customer, how would i group these and sum the QTY ?
Many thanks
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |