First vs Second purchase Products

[jmukhtar]

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,

[v-eqin-msft · Accepted solution]

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.

[PaulOlding]

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]

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

[askhanduja]

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]

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

[ValtteriN]

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]

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

[ValtteriN]

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]

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