Forum Discussion

user5341567's avatar
user5341567
Icon for Helper I rankHelper I
1 year ago
Solved

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

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.

Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    Try changing it to

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

5 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    You could create a column like

    Num order =
RANK (
    DENSE,
    ALL ( 'Table' ),
    ORDERBY ( 'Table'[Order number], ASC ),
    PARTITIONBY ( 'Table'[Customer] ),
)
    • user5341567's avatar
      user5341567
      Icon for Helper I rankHelper I
      1 year ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        1 year ago

        Try changing it to

        Num order =
RANK (
    DENSE,
    ALL ( 'Table'[Order number], 'Table'[Customer] ),
    ORDERBY ( 'Table'[Order number], ASC ),
    PARTITIONBY ( 'Table'[Customer] ),
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.