Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Customer 1st vs 2nd order behaviour ( excluding zero value orders)

Hi,

 

I am badly stuck again and I was hoping to get some help from this super supportive community.

 

I am trying to create a dashboard that will show how many customers had a discount applied on their first order and from those customers how many came back to do a 2nd purchase on full price and how many to buy with discount again.

 

I have been trying for the past few days with different queries to achieve it but nothing has worked.In order to do that I merged the orders with customer tables so every customer has a row with each order (each customer can have more than 2 orders most have around 20) so after i merged the tables I tried to rank each customers orders based on date hoping that it will mark the first order as 1 and the second as 2 then I was going to filter out zero value orders  and then create a query or a column which will show all customer orders that have as a ranking 1(which means is their first order) to state them as "first purchase discounted" if there was discount applied on the total value paid and for all the orders that have 2 as a ranking then I would do a similar statement where if the discount is greter than 0 the it will state second order as "discounted second order" or "non discounted second order" however with any way that I tried to do this didnt work.

 

Has anyone come acrooss something similar before? or have any suggestions

 

Any help would be much appreciated

 

Thank you

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    You can build a Rank column by calculated column and then build measure to achieve your goal.

    I build a sample to have a test.

    Rank column:

    Rank = RANKX(FILTER('Table','Table'[Customer]=EARLIER('Table'[Customer])),'Table'[order date],,ASC)

    Measures:

    Total Distinctcount Customer 1st Orders Bought With Discount Applied =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer] ),
    FILTER (
        ALL ( 'Table' ),
        AND ( 'Table'[Rank] = 1, 'Table'[Discount appliy] <> 0 )
    )
)
    Distinctcount OF Customers That Returned & Placed A Second Order Paying Full Price =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer] ),
    FILTER (
        ALL ( 'Table' ),
        AND ( 'Table'[Rank] = 2, 'Table'[Discount appliy] = 0 )
    )
)

    Result is as below.

    Best Regards,

    Rico Zhou

     

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

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Yeah.... I have a suggestion.

     

    1) Please give us representative data to work with in text form that's easy to copy.

    2) Give us a simple worked out example of what you really need. A picture is worth a thousand words.

     

    Such a simple example can be created in Excel. If you can't get the figures right in the model, you can just draw the desired figures in your picture. It's really hard to write DAX without any image of the underlying model.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I prepared an excel sample of the desired outcome as advised,that will hopefully make things a little bit more clear 🙂

       

       

       

      What I want essentially to see if from the customers that bought with discount given how many of them returned to buy on full price without any discount applied. 

      You will see some orders that have zero value but some discount applied these will be orders that I would like to filter out/exclude as they are orders that are send out as a part of promotions.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    You can build a Rank column by calculated column and then build measure to achieve your goal.

    I build a sample to have a test.

    Rank column:

    Rank = RANKX(FILTER('Table','Table'[Customer]=EARLIER('Table'[Customer])),'Table'[order date],,ASC)

    Measures:

    Total Distinctcount Customer 1st Orders Bought With Discount Applied =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer] ),
    FILTER (
        ALL ( 'Table' ),
        AND ( 'Table'[Rank] = 1, 'Table'[Discount appliy] <> 0 )
    )
)
    Distinctcount OF Customers That Returned & Placed A Second Order Paying Full Price =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer] ),
    FILTER (
        ALL ( 'Table' ),
        AND ( 'Table'[Rank] = 2, 'Table'[Discount appliy] = 0 )
    )
)

    Result is as below.

    Best Regards,

    Rico Zhou

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      That worked for me.

       

       

      Thank you sooooo much 🙂