Cross Selling Matrix Deep Dive - Power BI & DAX Tutorial

Cross Selling Matrix In Power BI Using DAX

6 comments

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.

cross selling

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.

cross selling

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.

cross selling

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).

cross selling

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.

cross selling

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

Enterprise DNA Power BI On-Demand

6 comments on “Cross Selling Matrix In Power BI Using DAX”

  1. How would the DAX change if you just wanted to know the absolute number of combinations sold, to a given customer?

    1. Hi, thanks for your interest in EDNA blogs.

      Based on your query description it is difficult to comprehend the exact requirement. In case you are checking the number of unique products a Customer Bought then use below DAX function and use in the “Customer” Visual shown in Blog.

      Distinct Products Purchases = DISTINCTCOUNT(Sales[Product Description Index]).

      If query is something, please explain further.

      For similar Analytical Scenarios, explore EDNA Course https://portal.enterprisedna.co/courses/305954

  2. Hey Sam,

    I’m using this tutorial to do a similar analysis. However, instead of finding the count of customers who purchased product (matrix row) and product (matrix col), I want the matrix to show the revenue received from these customers.

    I posted my question on PowerBI Community (https://community.powerbi.com/t5/Desktop/Market-Basket-Analysis-finding-the-revenue-from-clients-who/m-p/1613752) but haven’t gotten any answers yet so I’m posting a comment here hoping you’ll help me.

    I adapted from your ‘Purchased Both Products’ measure and from “https://blog.enterprisedna.co/discover-the-intersect-function-in-dax-and-how-to-utilize-it-power-bi-tutorial”. Now my measure looks like this:
    RevPurchaseBoth =
    VAR productrow= VALUES(data[Client])
    VAR productcol = CALCULATETABLE(VALUES(data[Client]), ALL(‘Product dim row’),
    TREATAS(VALUES(‘Product dim col'[Product]),data[Product]))

    RETURN
    CALCULATE(SUM(data[Revenue]), INTERSECT(productrow,productcol))

    The cells are showing up correctly (blank where it’s supposed to be blank, filled where it’s supposed to be filled), but the values in the cells are wrong. They are only showing the revenue of the productrow now. However , what I require is the sum of revenue from both productrow and productcol.

    I’m not sure where in my measure has gone wrong. Appreciate your input here! Thank you.

    1. Update: I got the values I wanted! But I don’t understand why it works.

      RevPurchaseBoth =
      VAR productrow= VALUES(data[Client])
      VAR productcol = CALCULATETABLE(VALUES(data[Client]), ALL(‘Product dim row’),
      TREATAS(VALUES(‘Product dim col'[Product]),data[Product]))
      RETURN
      CALCULATE(data[revrow]+data[revcol], INTERSECT(productrow,productcol))

      revrow = SUM(data[Revenue])
      revcol = CALCULATE(data[revrow],ALL(‘Product dim row’), TREATAS(VALUES(‘Product dim col'[Product]),data[Product]))

      Why does it work now when I created 2 separate measures for the revrow and revcol? It didn’t work when I put them as VARs in RevPurchasedBoth.

      1. Hello Adriel, thank you for posting your query onto our blog post.

        Well, this happens because of context transition. The context under the “VARIABLES” works differently in comparison to the “Measure Branching Technique”. And this is something that cannot be efficiently explained over here.

        We encourage you to please write back onto our Community Forum where our experts will be able to explain you with the examples of how actually “Context Transition” concept actually works in the Power BI. Below is the link of the forum provided for the reference.

        We’re also providing the link of our blog post that we’d created onto this topic for the reference.

        Hoping you find this useful and helps you in better understanding of the concept/fundamentals.

        Happy Learning!!!

        https://blog.enterprisedna.co/what-is-context-transition-and-why-does-it-matter/
        https://forum.enterprisedna.co/

    2. Hello Adriel, thank you for posting your query onto our blog post.

      Well, it’s always a little bit difficult to judge and provide the results without looking at the working of the PBIX file in this scenario since there’re so many variables involved in order to achieve this type of results.

      We encourage you to please write back to us onto our Community Forum by providing the working of the PBIX file along with the description of the problem that you’re encountering as well as the results that you’re trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.

      Hoping this helps you.

      Happy Learning!!!

      https://forum.enterprisedna.co/

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.