Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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_idcustomer_idorder_rank
32211001
32211001
45521002
44781003
15522511
15522511
62212512
75513501
75513501
85503502
85513503
91203504
91203504

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

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Picture1.png

 

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.


Go to My LinkedIn Page


View solution in original post

PaulOlding
Solution Sage
Solution 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])

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Solution Sage
Solution 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])
Jihwan_Kim
Super User
Super User

Picture1.png

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors