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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.