In this tutorial, I will teach you how to run a Basket Analysis in Power BI. 

The Basket Analysis uncovers the products bought together by consumers. In the context of a grocery store, for example, this analysis might show that whipped cream and berries are frequently bought together. 

Based on this result, you can:
– place the products close to each other (if in a physical store) to encourage cross-selling
– suggest the second product when the consumer places the first product in the cart (if in an online store)
– sell the products together
– offer discounts for buying the products together

This analysis is not limited to products. The basket analysis is also useful to explore the visited pages of a website, the questions raised in support tickets, and even the dishes ordered in a restaurant.

Below is what we will build together: a Basket Analysis Network and a Basket Analysis Chart.

Three key concepts are necessary to understand for basket analysis in Power BI: Support, Confidence, and Lift.

Support

This is the easiest of the three concepts. It is the percentage of transactions that include two specific products.

Using the example above, whipped cream and berries appear together in 0.9% of transactions therefore their support is 0.9%. In other words, on 1000 transactions there are on average 9 transactions with whipped cream and berries together.

Support is important for two reasons:

– support should be high enough to make the cross-selling opportunity financially interesting. Cross-selling products with very small revenue is probably not worth the effort

– product combinations that happen rarely (low support) should not be used because statistical analysis is not reliable on rare combinations.

Confidence

Confidence is the percentage of transactions that contain the two products together, out of the transactions containing one of the two products.
As a consequence, there are always two confidence numbers for each couple of products.

For the relationship between whipped cream and berries:
– 12.6% of consumers bought whipped cream and berries, out of all consumers that bought whipped cream
– 27.2% of consumers bought whipped cream and berries, out of all consumers that bought berries

Confidence is useful because it gives the direction of the cross-selling. We can make the hypothesis that it's easier to sell whipped cream to someone buying berries (whipped cream is a nice addition to berries), compared to the opposite. 

Confidence, however, does not give us the strength of the relationship between the products. One product might be a very common purchase so that it generates high numbers for confidence, independently from the second product.

Lift

Lift gives the strength of the relationship between a basket of two products. 

• A lift close to 1 indicates that there is no important relationship between the products.
• A number higher than 1 (positive relationship) indicates that the consumers buy the basket more frequently than it would happen by chance
• A number lower than 1 (negative relationship) indicates that the consumers buy the basket less frequently than it would happen by chance

Whipped cream and berries have a Lift of 3.8, meaning that consumers buy them together about 4 more times than it would happen by chance. This is a quite strong relationship.

To recap:

• Support indicates the frequency of the products being sold together. A high support means that the basket analysis statistics are more reliable. Additionally, higher support makes the basket of products more interesting financially.
• Confidence indicates the direction for cross-selling
• Lift indicates the strength of the relationship between the products

For a more detailed explanation of Support, Confidence and Lift you can check out the blog post here.

Let's now move to Power BI for the analysis.

Load the basket analysis data to Power BI

For this tutorial, I use the sample dataset available on the Salem Marafi blog. You can download the sample grocery dataset from here, to follow along with the tutorial.

Load and add an Index column

First, load the CSV file in Power BI by going to Get Data and then Text/CSV.

If your Power BI looks different, it's because I switched to the Power BI Updated Ribbon. 

You can activate the same ribbon by updating to the latest Power BI version, then going to File -> Options and Settings -> Options -> Preview Features and choosing Preview Ribbon. More info on that here.

Below is what the file looks like after you load it.

There is one row per transaction, and the columns have the products bought in the same transaction. For example, the transactions at row 10 contain whole milk and cereals.

The first step is to add an index column; the index column will allow us to analyze the transactions separately.

Go to the Add column and click on Index Column.

The result is the same table with an added index column. It doesn't matter if the index column is at the beginning or at the end.

Unpivot the table and remove unnecessary columns

The next step is to unpivot the columns with the products. Right-click on Index and choose Unpivot Other Columns.

The result is a table with three columns: the first column has the transaction index, the second one has the product number and the third one the name of the product.

The second column (Attribute) is useless so you can delete it. Right-click on Attribute and choose Remove. 

The Value column also has empty rows to delete. Click on the arrow next to it, then deselect (blank) and choose ok. This removes empty rows from the table.

The final step for the data loading is the rename the columns. Rename the first column to 'Transaction ID' and the second column to 'Item'. You can rename the columns by double-clicking on the column header.

We are done with the data preparation! Click on Close & Apply to go back to Power BI.

You should see the 'groceries' table loaded on the right side of the screen.

Create the basket analysis table in Power BI

The Basket Analysis table has one row for each combination of two products. Furthermore, the table has one column for each one of the indicators described above: Support, Confidence, and Lift.

The Basket Analysis table is created with a DAX formula. Click on Home, New Table.

In the textbox that appears, enter the following formula:

Basket analysis = 

FILTER(
CROSSJOIN(VALUES('groceries'[Item]),SELECTCOLUMNS(VALUES('groceries'[Item]),"Item2", [Item])),
[Item]>[Item2])

There are two parts to the formula: FILTER and CROSSJOIN. CROSSJOIN generates the table by creating one row for each couple of items. Specifically, it creates one row for whole milk plus citrus, one row for whole milk plus coffee, one row for citrus plus coffee, etc.

FILTER removes the duplicate rows. 

We now have a table with all possible combinations of products.

The following step is adding a calculated column with the concatenation of the two items. Click on New column (next to New table) and enter the DAX formula:

Basket = [Item]&" – "&[Item2]

This is the result.

Now let's move to the interesting part: the indicators formulas.

Add the Support column

The Support of the basket tells us the percentage of the transactions containing the two specific items. 

Here is DAX the formula:

Support basket = 

var item1=[Item]
var item2=[Item2]

var transactionsWithItem1 = SELECTCOLUMNS(FILTER('groceries','groceries'[Item]=item1),"transactionID",[Transaction ID])

var transactionsWithItem2 = SELECTCOLUMNS(FILTER('groceries','groceries'[Item]=item2),"transactionID",[Transaction ID])

var transactionsWithBothItems = INTERSECT(transactionsWithItem1,transactionsWithItem2)

RETURN COUNTROWS(transactionsWithBothItems)/ DISTINCTCOUNT(groceries[Transaction ID])

There are several variables in this formula: transactionsWithItem1, transactionWithItem2, and transactionsWithBothItems.

transactionsWithItem1 returns a table of all transactions including the first product. transactionsWithItem2 does the same with the second product. transactionsWithBothItems returns a table of the transactions including both products. 

Finally, COUNTROWS counts the number of transactions with both products and DISTINCTCOUNT counts the total number of transactions.

Now we move to the Confidence columns.

Add the Confidence columns

Confidence is equal to the percentage of transactions with both products (called Support of the basket) divided by the percentage of transactions with only one product (called Support of the product).

Below is the DAX formula for the Confidence of product one for the basket. The logic is exactly the same for product two.

Confidence Item 1 -> Basket = 

var item1 = [Item]
var numberOfTransactions = DISTINCTCOUNT(groceries[Transaction ID])

var supportItem1 = COUNTROWS(FILTER('groceries','groceries'[Item]=item1))/ numberOfTransactions

RETURN [Support basket]/supportItem1

Add the Lift column 

The formula for the Lift is the following.

Let's take the example of unrelated products: for those products, the percentage of times the products are bought together (support of basket) should be similar to the percentage of times product 1 is bought, multiplied by the percentage of times product 2 is bought.

In this case, the numerator and denominator in the formula have similar values and therefore the value of Lift is close to one. A value of Lift close to one indicates no relationships.

When Lift is higher than one, the two products are bought together more times than would happen by chance. A lift of 4, for example, indicates a strong relationship: the products are bought together four times more often than would happen by chance.

Below is the DAX formula for Lift.

Lift = 

var item1 = [Item]
var item2 = [Item2]

var numberOfTransactions = DISTINCTCOUNT(groceries[Transaction ID])

var supportItem1 = COUNTROWS(FILTER('groceries','groceries'[Item]=item1))/numberOfTransactions

var supportItem2 = COUNTROWS(FILTER('groceries','groceries'[Item]=item2))/numberOfTransactions

RETURN [Support basket]/(supportItem1*supportItem2) 

Now we have all three indicators, and we can move on to visualizations.

Add the Basket Analysis Network visualization to Power BI

The network chart is not a standard visualization so it needs to be imported from the marketplace.

Search for Network and add the Advanced Network Visual (Light Edition).

Add the Network Chart to your report and configure it this way: drag and drop Item and Item2 in Nodes, then Lift in Measure.

In the filters, add a filter on both Lift and Support basket. In this tutorial, filters of 2.2 for Lift and 0.6% for Support basket work well. The correct filters, however, depend on the data analyzed.

Below is the result!

You can use the bar on the left to zoom in and out. The size of the bubbles is equal to the Lift.

There is one thing to be careful about: when a product has connections with multiple products, the number shown is the sum of the Lift for the various connections. This number is meaningless.

For example, the chart shows a Lift for the whipped cream of 16: this is the sum of the Lift between whipped cream and butter (2.6), between whipped cream and curd (2.7), etc.  

Even if the number itself is meaningless, a bigger number means that the product has important Lift (relationships) with other products therefore the product deserves further analysis.

Now on to the final visualization.

Add the Basket Analysis Chart visualization to Power BI

The Basket Analysis Chart is a traditional Scatter chart, with the filters on Lift and Support Basket as before.

You can add the Confidence columns in the Tooltip section so you are able to see Confidence information when you hold the mouse on a chart point.

And here is the result!

You can now analyze your product sales and find cross-selling opportunities! I hope this article was helpful to you.

Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.