RANKING ORDERS BY CUSTOMER

[Anonymous]

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

[Jihwan_Kim · Accepted 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 

[Anonymous]

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

[PaulOlding · Accepted solution]

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])

[Jihwan_Kim · Accepted 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