Forum Discussion
RANKING ORDERS BY CUSTOMER
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
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
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
calc_order_rank =RANKX(CALCULATETABLE('Table', ALLEXCEPT('Table', 'Table'[customer_id])),'Table'[order_id],, ASC, Dense)If you have an order date in a Orders table you'd swap 'Table'[order_id] for RELATED(Orders[order_date])
3 Replies
- Jihwan_Kim
Super User
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
PaulOldingSolution Sage
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
calc_order_rank =RANKX(CALCULATETABLE('Table', ALLEXCEPT('Table', 'Table'[customer_id])),'Table'[order_id],, ASC, Dense)If you have an order date in a Orders table you'd swap 'Table'[order_id] for RELATED(Orders[order_date])