Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Cross Selling Matrix In Power BI Using DAX

by | 9:00 am EDT | May 21, 2020 | Power BI

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.

## 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

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...