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 wo...
  • 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.

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