Creating a Basket Analysis that Customers who buy X also LATER bought Y

Question

I am trying to make a visual in Power BI that shows me what people order second, if they order x product first. Very similar to a basket analysis but I don't want to know what was in the same cart as much as what they bought when they came back a second time, given what they bought the first time.

It is important that I be able to click the first product in a Slicer as a form of filtering.

For example, if I click 'Steak' in a Slicer, that needs to set the visual to show me: of the people who ordered Steak first, what did they order second (as a Count)? -> Example: 2 ordered Mashed Potatoes and 1 ordered Green Beans.

This will help me see what to upsell customers, given their first order.

I have the following example table information to work with

Customer	Index of Order (By Customer)	Product
Greg	1	Steak
Cindy	1	Steak
John	1	Pork
Greg	2	Mashed Potatoes
Greg	3	Cake
Cindy	2	Mashed Potatoes
Susie	1	Steak
John	2	Mashed Potatoes
Susie	2	Green Beans

do you want to see only immediate next order, or all subsequent? (e.g. Greg ordered Steak - mashed potato - Cake; selecting steak, do you want to see cake too?). Do you want to filter on first orders only (index = 1) or all orders which were not last orders (e.g. shall mashed potato be shown on the slicer)? — Máté Juhász
– Máté Juhász, Commented Nov 12 at 20:49
Hey Máté, I would like to see only their next order. I only want the first order to be the parameter for filtering, though, so in this example- Mashed Potatoes would not be shown in the Slicer. Ideally the Slicer should show just Steak or Pork and when the user selects one they are given a count of how many people ordered Mashed Potatoes, Green Beans, Cake (when applicable), etc, as their second order. Edited to fix a confusion — Michael Marhefka
– Michael Marhefka, Commented Nov 12 at 20:57

Máté Juhász · Accepted Answer · 2025-11-12 21:07:38Z

I'd create a transformed table already in Power Query, with a code like below:

let
    Source = << your data >>,
    first_order = Table.SelectRows(Source, each ([#"Index of Order (By Customer)"] = 1)),
    subsequent_order = Table.SelectRows(Source, each ([#"Index of Order (By Customer)"] > 1)),
    all_orders = Table.NestedJoin(first_order, "Customer", subsequent_order, "Customer", "subsequent"),
    #"Renamed Columns" = Table.RenameColumns(all_orders,{{"Product", "first Product"}}),
    #"Expanded subsequent" = Table.ExpandTableColumn(#"Renamed Columns", "subsequent", {"Product"}, {"subsequent product"}),
    #"Grouped Rows" = Table.Group(#"Expanded subsequent", {"first Product", "subsequent product"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

With that you can add a filter on first order and display sum of counts for subsequent orders.

Collectives™ on Stack Overflow

Creating a Basket Analysis that Customers who buy X also LATER bought Y

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related