In this tutorial, I show you how to create a cross selling matrix in Power BI. This is just a preview of a session that I conducted in an Enterprise DNA Learning Summit. You may watch the full video of this tutorial at the bottom of this blog.
This technique that I’ll demonstrate can add a lot of value to your marketing and sales activities, knowing which products are bought with other products. The very first thing to do to achieve this is to set up the model.
Setting Up The Model
This a very detailed model that we have here, which I used during the Learning Summit, but we’re going to focus on the Products table and the Comparison Products table for this tutorial.
We need these two tables to be along all the rows and along all of the columns.
The Comparison Products table is a key set up for this cross selling matrix. It is exactly the same as the Products table, like it’s generic. I’ve created it utilizing this formula SUMMARIZE, which returns a table based on whatever we want from that particular table. In this case, we want the index in the Product name.
We’re going to use this index across the top of our matrix and we’re going to use the Product name across all the rows.
Note that this table has no relationship to the core model. We’re going to integrate some filters from this table into our core model via a formula.
Understanding context is really key to getting this.
Now, we create the Customer List based on the products using this formula. We use VALUES, which returns a table of unique items based on the filters that are placed on it.
So, we’re creating a table of how many unique customers bought a particular product. For instance, for Product 2, it’s 688. Then, we’re just counting up that table.
Creating The Matrix
To get a matrix, we need to look at this Customer Purchases list table and the virtual table that is being filtered by the Comparison Products. And this is the key formula.
Now let’s look at our tables here, specifically this result, 109. This 109 result is a combination of the list of customers that are creating the 688 result (Product 2) and the list of customers that are creating the 636 result (Product 1).
It’s working out how many customers purchased Product 2, but also purchased Product 1. And we need to compare these lists. Creating this list of Product 2 is easy because the filter being placed here is naturally occurring and it’s going to naturally filter the 688 result.
We also manufacture some context for this Product 1 (636), which is being represented by this index number of 1, via this particular formula, ComparisonPurchase.
With the ALL function, we remove any context that might be naturally occurring via our model, which has been placed over by Product 2 (109). Then, we re-apply context via a virtual relationship using the TREATAS function. I prefer this method that uses TREATAS because it simplifies things and it creates a much cleaner model.
TREATAS creates context or filters virtually in our model within a formula instead of having to physically do it.
With this formula, we’re creating two lists of customers, no matter where we are in the matrix. So for 109, the variable (VAR) InitialPurchase is calculating the customers who purchase Product 2, and the variable ComparisonPurchase is calculating the customers who purchase product 1.
Then, the other key part of the calculation is with the use of the INTERSECT function to work out which customers appear in our InitialPurchase table and in our ComparisonPurchase table. That gives us the combined customers who purchase both of those products and that happens again and again throughout this entire matrix.
***** Related Links *****
Basket Analysis Example – Power BI Advanced Analytics
Basket Analysis Introduction – Best Practice Tips For Power BI
Discover Multiple Product Purchases Using DAX In Power BI
Conclusion
This tutorial is just a bit of a preview on how to create cross selling matrix in Power BI. There’s a quite a lot to it, but I wanted to give you an idea of how this actually works. I have been asked about cross selling matrixes many times, so I though it would be great to give an overview of how it’s done in Power BI.
This is a great way of running a cross selling analysis and get terrific insights. You’ll get the percentages and see what’s a good up-sell opportunity for particular clients, for example. If you want to learn more on this technique, check out the links below.
Cheers!
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events
