Power BI market basket analysis: find what sells together
Power BI market basket analysis finds pairs of products that customers buy in the same order more often than chance would suggest. Retailers and distributors use it to plan bundles, shelf placement and cross-sell prompts. The method needs only order lines, and our consultants build it inside your own Microsoft tenant with the scope agreed in writing first.
- Support, confidence, lift
- Pairs ranked by strength
- Built from order lines
Last updated
What is Power BI market basket analysis?
Every order is a basket. Some products land in the same basket again and again. A buyer of a printer often adds ink, and a buyer of paint often adds brushes. Those pairs seem obvious, but many useful ones are not.
Basket analysis counts the pairs across all orders and ranks them. Then a merchandiser sees which items to place together, and an online team sees which item to suggest at checkout. So the insight leads straight to an action.
The same idea works over time as well. Instead of one order, you look at everything a customer bought within a period. As a result, a distributor can spot customers who buy product A but never the product B that usually goes with it.
Three association measures and how to read them
These definitions are standard in association analysis, and all three appear on the page together.
Support
Divide the baskets that contain both products by all baskets. It shows how common the pair is.
Confidence
Divide the baskets with both products by the baskets with the first product. So it shows how often A brings B along.
Lift
Divide confidence by the share of all baskets that contain B. A value above one means the pair occurs more often than chance.
Baskets with both
Show the raw count too, because a high lift on a handful of orders proves little.
Attach rate
For a chosen main product, list the share of its orders that include each accessory.
Missed pairs by customer
List customers who buy one half of a strong pair and not the other, since these make a ready call list.
How do you model market basket pairs in Power BI?
The challenge is that one product table cannot filter two things at once. A user wants to pick product A and then see every product B next to it. So the model holds a second copy of the product table for the other side of the pair.
We relate that copy to the sales lines with an inactive relationship. A measure then activates it when needed, which the DAX function USERELATIONSHIP allows. With this in place, the measure counts orders that contain both the selected product and the comparison product.
Scale is the second concern. The number of possible pairs grows fast with the size of the range. Therefore, for a large catalog we analyze at category or subcategory level first. For item level, we often compute pair counts ahead of time in a query or database and load the result. If your analysts will maintain these measures later, power bi dax consulting can review and tune them first.
Pitfalls in Power BI market basket analysis
The method is simple, but these six traps produce misleading pairs.
- Ranking by lift alone and surfacing pairs with very few orders
- Pairing a product with itself
- Letting one very popular item appear in every result
- Analyzing item level when the catalog is too wide to run well
- Counting line quantity instead of distinct orders
- Reading a pattern as a cause without a test
How we set up Power BI market basket analysis
We prove the method on one category before widening it.
Define the basket
First we agree what a basket is: one order, one visit or one customer over a period.
Build the pair model
We add the second product table, the inactive relationship and the three measures, then test them on known pairs.
Tune for speed
Next we check response times and move heavy pair counts upstream when the catalog demands it.
Turn pairs into actions
Finally we design pages for merchandising and sales, including the customer call list, and hand over the file.
Basket analysis in Power BI: questions from retail teams
What data does Power BI market basket analysis need?
It needs order lines with an order number and a product. A date and a customer help, but the core method works without them. Because the measures count distinct orders, quantity and price are optional. So most companies already hold everything required in their sales or point-of-sale system.
Is market basket analysis fast enough for a big catalog?
It depends on the level. Category pairs respond quickly in almost any model. Item pairs across a very large range can strain a measure, since every pair needs its own count. In that case we compute the pairs during data preparation. Then the report only reads a small, ready table.
What does a lift value really mean?
Lift compares how often two products sell together with how often they would if purchases were unrelated. A value above one suggests a link, and a value below one suggests the products replace each other. However, lift says nothing about volume. Therefore we always show it beside support and the raw count.
Can we filter the pairs by store or season?
Yes. Because the measures run inside the model, any slicer changes the baskets they count. You can compare stores, regions, channels or months. Still, small slices hold few orders, so pairs become less reliable. The page therefore shows the basket count for the current selection at all times.
How do we start?
With a free 30-minute assessment. Tell us about your order data and catalog size, and we reply within 2 hours. Power BI Agency is a division of Sataware Technologies and independent of Microsoft. We deliver online to clients with weekly demos. The phone number is (480) 485-7373.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.