How to count/group orders by customers in sequential order?

[user5341567]

Hi all,

I have something which I thought would be a relatively easy solution, but I haven't been able to find any posts or a way to successfully do this after doing a ton of research. Has anyone run into this before?

I have a table with order number, customer number, and transaction date, and I need to count/group the number of orders per customer in sequential order, based on order date. E.g., for a customer number, if they have placed 4 orders, I'd expect to see 1, 2, 3, 4 next to the order number for a particular customer.

Here is an example dataset - hoping to calculate the 'Orders' column:

Thank you so much for looking into this! Any input is greatly appreciated.

[v-jialongy-msft]

Hi @user5341567 

This is my sample data:

 
You can get the result you want by using the following calculate column:

Column = RANKX(FILTER(ALL('Table'),'Table'[CustomerNumber] = EARLIER('Table'[CustomerNumber])),'Table'[OrderNumber],,ASC,Dense)

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

You could create a column like

Num order =
RANK (
    DENSE,
    ALL ( 'Table' ),
    ORDERBY ( 'Table'[Order number], ASC ),
    PARTITIONBY ( 'Table'[Customer] ),
)

[user5341567]

Thank you so much for your response! I got a Circular Dependency error unfortunately - perhaps because I sometimes have several of the same order number? 

[johnt75 · Accepted solution]

Try changing it to

Num order =
RANK (
    DENSE,
    ALL ( 'Table'[Order number], 'Table'[Customer] ),
    ORDERBY ( 'Table'[Order number], ASC ),
    PARTITIONBY ( 'Table'[Customer] ),
)

[user5341567]

That worked perfectly. Thank you for your response!