Forum Discussion

jmukhtar's avatar
jmukhtar
Regular Visitor
4 years ago
Solved

First vs Second purchase Products

Hi

 

I am working on a requriement to generate visualize what products customer bought in second orders based on products that are purchased in first order and i am really struggling to get this working. 

 

If a 100 customers bought product A in first order and out of those 100 customers 50 bought Product B and 30 bought Product C and 20 bought Product D in second orders. This neeeds to be presented like this 

 

 

 

Data that i currently have looks like this where sortorder is the first or second order number for the customer

 

 

 

 

We have full control of data and have all of sales and product data available to us and can format data as we require. 

 

I have tried quite a few things but not able to get this working properly. 

 

Any help on how to do it would be really really helpful

 

Thanks,

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi jmukhtar ,

     

    I have created a data sample,

    According to this requirement: we are looking for a list of customers who have bought a specific product in first order and then use that list to see what these customers have bought in second order

     

    Please follow these steps:

    1. Create a productId table for slicer

    ForSlicer = VALUES('Table'[ProducrId])

    2. Rank by Date

    Rank = RANKX (ALL('Table'), CALCULATE ( MAX ( ( 'Table'[Date]) ) ),,ASC,Dense)

    3. First flag measure , and apply it to filter pane, set as "is 1"

    First = IF(MAX('Table'[ProducrId]) in ALLSELECTED(ForSlicer[ProducrId]),[Rank],BLANK())

    4. Second flag measure, and apply it to filter pane, set as "is 1" as well

    Second = 
var _allCustomers=SUMMARIZE(FILTER(ALL('Table'),[First]=1),[CustomerID])
return IF(MAX('Table'[CustomerID]) in  _allCustomers && [Rank]=2,1,0)

     

    Outputs:

     

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

9 Replies

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi jmukhtar 

     

    Here's an option for this.  It requires you to import a second copy of your products table that is disconnected from the rest of your model

    Then you can have a Second Order Amount measure like this:

    Second Order Amount = 
VAR _Customers =
CALCULATETABLE(
    VALUES('Table'[customer_id]),
    'Table'[sort_order] = 1
)
VAR _Result = 
CALCULATE(
    SUM('Table'[net_sales]),
    'Table'[sort_order] = 2,
    _Customers,
    TREATAS(VALUES('Products 2'[product_name]),'Products'[product_name])
)
RETURN
    _Result

     

    Finally, you'd use the product name from Products 2 table in your visual, along with the above measure.

     

     

     

    • kk_shp_user's avatar
      kk_shp_user
      Icon for Helper I rankHelper I
      1 year ago

      Thanks for this approach! I found this easier!

      Also, I used this to count distinct customers instead of sales. The only change I did was in the second order measure, I had to include ALL(Table), so that the filters for the first  product did not affect this measure.

      Second Order Amount = 
VAR _Customers =
CALCULATETABLE(
    VALUES('Table'[customer_id]),
    'Table'[sort_order] = 1
)
VAR _Result = 
CALCULATE(
    SUM('Table'[net_sales]),
    ALL('Table'),
    'Table'[sort_order] = 2,
    _Customers,
    TREATAS(VALUES('Products 2'[product_name]),'Products'[product_name])
)
RETURN
    _Result



  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi,

    Here is one way to do this:

    Start data:


    Dax (measure):

    FirstOrder =
    var Fdate = calculate(FIRSTDATE(FirstOrder[Date]),all(FirstOrder),FirstOrder[Customer]=max(FirstOrder[Customer]))
    var Sdate = CALCULATE(FIRSTDATE(FirstOrder[Date]),all(FirstOrder),FirstOrder[Date]>Fdate,FirstOrder[Customer]=max(FirstOrder[Customer]))
    var Cdate = MAX(FirstOrder[Date])
    return

    switch(true(),
     
    Cdate=Fdate,"First Order",
    Cdate=Sdate,"Second Order",
    "Other")
    End result:
    Note that you need to use filter similar to the one in the picture to get the desired output.

     

    I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

    • jmukhtar's avatar
      jmukhtar
      Regular Visitor
      4 years ago

      Hi ValtteriN 

       

      This doesn't work for us as we are looking for a list of customers who have bought a specific product in first order and then use that list to see what these customers have bought in second order

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi,

    In that case you can add this table:


    Then you can use that in a table to get the list of customers who bought e.g. Product C in first order:


    (add this to table visual ^^)

    OrderTypeFilter = IF(
    OR(HASONEVALUE('Order Type'[Order Type])=FALSE(),
    MAX('Order Type'[Order Type])=FirstOrder[FirstOrder]),1,0)

    Now edit interactions to disable product filter from second visual:

     


    Filter second visual with "second"

     


    And as a result we notice that customers A and B bought product C in the first order. Out of these customers A bought product D in second order.

    Data matches this:

     

    • jmukhtar's avatar
      jmukhtar
      Regular Visitor
      4 years ago

      Hi ValtteriN 

       

      This solution gives us the first vs the second purchase, but What we want to get in second order visual is only the customers who have purchased from the selected category in first order. Please see the snapshot 

       

       

      1,3,6,8,9,10 customers bought product A in first order; and these customers bought product B, C and D in second order.

       

      In second visual we want to see products bought by customers who have bought product A in first order

  • askhanduja's avatar
    askhanduja
    Icon for Helper I rankHelper I
    4 years ago

    It would be helpful if you can attach a Power BI file with some sample data and screenshot of the desired output based on that sample data

    • jmukhtar's avatar
      jmukhtar
      Regular Visitor
      4 years ago

      Sample data is shared along with the requirements. I couldn't get it working on Power BI so far 😞 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi jmukhtar ,

     

    I have created a data sample,

    According to this requirement: we are looking for a list of customers who have bought a specific product in first order and then use that list to see what these customers have bought in second order

     

    Please follow these steps:

    1. Create a productId table for slicer

    ForSlicer = VALUES('Table'[ProducrId])

    2. Rank by Date

    Rank = RANKX (ALL('Table'), CALCULATE ( MAX ( ( 'Table'[Date]) ) ),,ASC,Dense)

    3. First flag measure , and apply it to filter pane, set as "is 1"

    First = IF(MAX('Table'[ProducrId]) in ALLSELECTED(ForSlicer[ProducrId]),[Rank],BLANK())

    4. Second flag measure, and apply it to filter pane, set as "is 1" as well

    Second = 
var _allCustomers=SUMMARIZE(FILTER(ALL('Table'),[First]=1),[CustomerID])
return IF(MAX('Table'[CustomerID]) in  _allCustomers && [Rank]=2,1,0)

     

    Outputs:

     

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