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 everyone,
I have a classical customers and sales table.
I would like to know in my sales tables (in a calculated column) for each customers which order is the first, which order is the second, the third ect...
This means I want to rank my orders/customers from the oldest to the most recent.
order_id | customer_id | order_rank |
3221 | 100 | 1 |
3221 | 100 | 1 |
4552 | 100 | 2 |
4478 | 100 | 3 |
1552 | 251 | 1 |
1552 | 251 | 1 |
6221 | 251 | 2 |
7551 | 350 | 1 |
7551 | 350 | 1 |
8550 | 350 | 2 |
8551 | 350 | 3 |
9120 | 350 | 4 |
9120 | 350 | 4 |
It's a little bit hard for me to explain in english what i need but i hope its okay this way.
Thanks a lot for your help,
Regards
Solved! Go to Solution.
order-rank CC =
VAR currentcustomer = 'Table'[customer_id]
RETURN
RANKX (
FILTER ( 'Table', 'Table'[customer_id] = currentcustomer ),
'Table'[order_id],
,
ASC,
DENSE
)
https://www.dropbox.com/s/6uv76w7q1p91ypg/paulmight.pbix?dl=0
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi. I'm not clear how you know what the most recent order is. Is it based on the order_id as a number, or is there an order table with a date on it?
This just uses the order_id
Hi,
Thank you for your replys, both solutions works perfectly.
What if i want to rank my orders only for the year 2021 ? first order of 2021 ect..
Regards,
Paul
Hi. I'm not clear how you know what the most recent order is. Is it based on the order_id as a number, or is there an order table with a date on it?
This just uses the order_id
order-rank CC =
VAR currentcustomer = 'Table'[customer_id]
RETURN
RANKX (
FILTER ( 'Table', 'Table'[customer_id] = currentcustomer ),
'Table'[order_id],
,
ASC,
DENSE
)
https://www.dropbox.com/s/6uv76w7q1p91ypg/paulmight.pbix?dl=0
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |