Boosting Sales and Customer Satisfaction: The Power of Cross-Selling in Fashion Retail

[Ilgar_Zarbali]

Cross-selling is a sales strategy that encourages customers to purchase additional, complementary products alongside their primary purchase. By suggesting relevant items that enhance the main product, businesses can boost revenue and improve customer satisfaction. For example, recommending matching cufflinks or socks when someone buys sneakers is a practical cross-selling approach. This method not only increases sales but also introduces customers to products that complement their purchase, benefiting both the business and the buyer.

In this article, I will create a Cross-Selling Matrix using essential DAX formulas.

You can access the downloadable materials through the following link:

Downloadable Materials 

The primary tool for analysis in such cases is the Cross-Sell Matrix – a table that helps visualize opportunities for cross-selling between different products or product categories. This table highlights which products sell well together and which do not. For example, it can indicate whether a customer who buys a shirt should be recommended cufflinks, or if these products should be placed next to each other on a website or store shelf.

The structure of this table is straightforward: the same set of products is arranged along both the rows and columns. At the intersection, a numerical indicator represents cross-selling performance – for instance, the number of customers who purchased both products shown on the X and Y axes. Naturally, this square matrix is symmetric along the diagonal, with cells on the diagonal often left blank.

Let's explore how to calculate and build such a table using DAX.

For demonstration, I will use a sample Fashion Retailer’s Dataset containing 10 unique products.

We have an fSales transactional table that includes columns for Products, Customers, Quantity, and more.

Additionally, there is a dProducts dimensional table listing 10 unique products, along with information on brand, product subcategory, product category, and other attributes.

These two tables are connected in the data model through the products column.

We will need to duplicate the product reference table so that the products can be placed along both axes of our future Cross-Sales Matrix. The simplest way to do this in Power BI is by selecting "Table" from the "Home"tab.

Next, we click on "New Table", and in the formula bar, we enter:

dProducts (CS) = dProducts

The newly created table serves as a helper table and will not be linked to any other tables for the purpose of building the Cross-Sales Matrix.

Now, let's return to the canvas and add a Matrix Visualization.

Let's Add the Products from the dProducts table to the rows of the Matrix, and add the Products from the dProducts (CS) table to the columns of the Matrix visualization.

Since the tables are not connected, Power BI cannot fully display the Matrix.

This issue will be resolved by writing a DAX formula.

We need to write a measure that calculates the number of customers who purchased both products at the intersection of each row and column in our matrix.

Let's create a measure in the Cross-Selling Measures table.

I will name this new measure Cross Selling.

Before writing the measure, let's review the following steps: I will begin with a VAR to store the unique customers who purchased the first product from the dProducts table, which appears in the rows of our matrix.

The initial VAR will look like this:

VAR CustomersWhoBoughtProduct1 = VALUES(fSales[Customer])

The dProducts (CS) table is not joined with fSales.

First, we need to use the CALCULATETABLE function, which will take a table of unique customers as the first argument. Here, we will use the VALUES function to extract distinct Customers from the fSales table.

Next, we will apply a couple of filters to this table.

• First, all existing filters in the model need to be removed. This can be done using the REMOVEFILTERS function, specifying that filters should be removed from the dProducts table.
• After removing the filters, we will apply new ones. Specifically, a filter on the second product from the duplicate dProducts (CS) table needs to be added.

The TREATAS function will help with this by applying filters from one table to another, even if the tables are not connected in the model.

• The first argument of TREATAS specifies the condition we want to apply – all values from the dProducts (CS) table in the Products column.
• The second argument is the table to which we apply the condition – in this case, fSales and its Products column.

We close the CALCULATETABLE bracket and proceed to output the results using the RETURN command.

Cross-Selling = 
VAR CustomersWhoBoughtProduct1 = VALUES(fSales[Customer])
VAR CustomersWhoBoughtProduct2 =
    CALCULATETABLE(
       VALUES(fSales[Customer]),
       REMOVEFILTERS(dProducts),
       TREATAS(VALUES('dProducts (CS)'[Products]),fSales[Products])
    )
RETURN
    INTERSECT(CustomersWhoBoughtProduct1,CustomersWhoBoughtProduct2)

Essentially, all that remains is to find the common elements between the two lists created in the variables CustomersWhoBoughtProduct1 and CustomersWhoBoughtProduct2.

This can be easily achieved using the INTERSECT function in DAX by specifying the variable names as the two arguments.

Note that the INTERSECT function returns a table – a list of intersections. However, we are only interested in the number of rows in this table, representing the number of customers who bought both products.

To count the rows, we simply wrap the INTERSECT function in COUNTROWS.

Cross-Selling = 
VAR CustomersWhoBoughtProduct1 = VALUES(fSales[Customer])
VAR CustomersWhoBoughtProduct2 =
    CALCULATETABLE(
       VALUES(fSales[Customer]),
       REMOVEFILTERS(dProducts),
       TREATAS(VALUES('dProducts (CS)'[Products]),fSales[Products])
    )
RETURN
    COUNTROWS(INTERSECT(CustomersWhoBoughtProduct1,CustomersWhoBoughtProduct2)) 

Let's press Enter and see how it all works.

Let's drag the measure we created into the values area of the matrix, and we’ll have an almost complete cross-sales matrix.

Next, let’s adjust the design slightly. Let’s turn Totals off.

It also makes sense to tweak the formula a bit to handle the diagonal values by replacing them with blanks.

To do this, we’ll return to the measure formula and add an IF function after the RETURN.

If the value selected in the current cell is the same – which we can get using the SELECTEDVALUE function – the formula will handle it accordingly.

If(SELECTEDVALUE(dProducts[Products])=SELECTEDVALUE('dProducts (CS)'[Products]),
   Blank(),
   COUNTROWS(INTERSECT(CustomersWhoBoughtProduct1,CustomersWhoBoughtProduct2)))

Now our matrix will have blank cells.

Conditional Formatting can be used to highlight both small and large numbers in the Matrix.

To do this, in the selected Matrix, hover over the values, right-click on Conditional Formatting, and choose Font color.

We can select white for the lowest values and light blue for the highest values.

The Matrix will appear as follows:

In this article, I have referenced educational materials from the following sources:

• Enterprise DNA – Advanced Analytics Power BI Course (https://app.enterprisedna.co/app/player-course/10?chapter=170&item=2326) – in English
• PlanetaExcel – Матрица кросс-продаж в Power BI и Excel (https://www.youtube.com/watch?v=laExJ1M0xbQ) – in Russian

Conclusion:

The Cross-Selling Matrix is a powerful tool for uncovering product relationships and identifying sales opportunities in Power BI. By leveraging DAX functions like CALCULATETABLE, TREATAS, and INTERSECT, you can dynamically analyze customer purchasing patterns, enhancing your ability to recommend complementary products. This not only boosts sales but also improves the overall customer experience. Mastering the creation and formatting of Cross-Selling Matrices is invaluable for any data professional looking to drive insights and maximize revenue. Let us know if these techniques were helpful – we’d love to hear your feedback!