Tag: cross selling

Cross Selling Matrix Deep Dive – Power BI & DAX Tutorial (Market Basket)

Posted on by Annie D

Understanding the Cross Selling Matrix in Power BI

A cross selling matrix is a powerful visualization used in sales analytics to identify which products are often purchased together by the same customers over a selected period. This form of basket analysis helps businesses uncover cross-selling opportunities, promotional ideas, boost revenue, and optimize product strategy.

The video demonstrates not just the process of creating a cross selling matrix in Power BI, but dives deep into the crucial concepts of DAX, context transition, and relationship management needed to generate accurate, actionable insights.

Key Steps and Concepts

1. Foundational Understanding: Context in Power BI & DAX

  • Context determines how your formulas and visuals behave. The row and column headers of your matrix create unique contexts for every cell, impacting which data is aggregated or filtered.
  • Proper understanding of context ensures your DAX calculations are returning meaningful results for each product pairing in the matrix.

2. Core Calculation: Customers Who Purchased Both Products

  • The goal is to find out, for any intersection in the matrix, how many customers bought Product A (row) AND Product B (column) within the selected date range.
  • This is done by creating two tables:
  • Table 1: All customers who bought Product A.
  • Table 2: All customers who bought Product B.
  • The INTERSECT function is then used to find customers common to both tables.
  • The final result is a COUNTROWS(INTERSECT(…)), revealing the number of unique customers who purchased both products.

3. DAX Techniques Used

  • VALUES(): Used to dynamically return a list of customers filtered by the current context (product, time frame, etc.).
  • CALCULATETABLE(): Allows creation of virtual tables filtered by specific product or comparison product context.
  • TREATAS(): Establishes virtual relationships between tables where no direct relationship exists, vital for comparing separate product lists.
  • ALL() or ALLEXCEPT(): Used to remove or adjust existing model relationships temporarily, isolating the proper comparison across products for accurate results.

4. Supporting Table for Comparison

  • To evaluate pairwise cross-selling (row vs column), a comparison products table is created, usually replicating your products dimension but used solely for comparison logic.
  • This table is not physically related to the sales table, so relationships are built on-the-fly in DAX using TREATAS.

5. Dynamic Filtering and Analysis

  • The entire technique is dynamic, meaning selecting different dates or filters in your Power BI report instantly recalculates the matrix.
  • This adaptability makes the matrix valuable for both exploratory analytics and operational dashboards.

Why Build a Cross Selling Matrix?

  • Reveal Product Affinities: Quickly see which items are often bought together, ideal for bundle promotions and recommendations.
  • Drive Sales Strategies: Identify which products could benefit from cross-promotion or upselling.
  • Customer Insight: Understand multi-product purchasing behavior within your customer base.

Example DAX Pattern for Purchased Both Products

Purchased Both Products = 
VAR Customers_ProductA =
    VALUES(Sales[CustomerID]) // For current row product context
VAR Customers_ProductB =
    CALCULATETABLE(
        VALUES(Sales[CustomerID]),
        TREATAS(VALUES('Comparison Products'[ProductID]), Sales[ProductID])
    )
RETURN
    COUNTROWS(INTERSECT(Customers_ProductA, Customers_ProductB))
  • Replace column/table names as per your own model.
  • Adjust context and relationships as necessary for your specific data schema.

Takeaway

By mastering this advanced cross selling matrix technique and the supporting DAX concepts (like context, table functions, and virtual relationships), you empower yourself to unlock powerful, nuanced insights into customer behavior and product performance using Power BI.

If you’re keen to further enhance your skills on this, Enterprise DNA is a superb resource! Check them out and watch their video on this below! 🙂

[1] https://www.youtube.com/watch?v=iZJz30LSik4