Often with my clients, I am asked to do market basket analysis. In other words, the client wants to find products that are often purchased together to help identify patterns in what people buy. In Tableau, we often filter dashboards based on a particular field. In this case, we want to find instances where the filter is true but also allow the user to select another item that must also be purchased on the same order. Ann Jackson provided this week’s WorkoutWednesday challenge to do just that.

The dashboard is made up of three views:

• BAN
• Order Bar Graph
• Viz in Tooltip

BAN

The BAN contains four measures:

# of Orders – Counts the distinct number of Order IDs.

COUNTD([Order ID])

% of Total Orders

First, create a set from Products (called Select Products). This set will be used as the filter for dashboard. To calculate the % of Total Orders, count the distinct number of Order IDs for the selected products, divide this by the total of distinct count of Order Ids. Use a fixed LOD calculation to count all order IDs and not just those from the set.

COUNTD(IF [SELECT PRODUCTS] THEN [ORDER ID] END)/SUM({FIXED : COUNTD([ORDER ID])})

Avg Order Amount

Use a Fixed LOD calculation to first calculate the sum of sales for each Order ID, and then average those order sales.

AVG({FIXED [ORDER ID]: SUM([Sales])})

Avg Order Quantity

Similarly, use a Fixed LOD calculation to calculate the total quantity by Order ID, and then average those order quantities.

AVG({FIXED [ORDER ID]: SUM([Quantity])})

Order Bar Graph

Create a multi-axis chart showing Sum([Sales]) and Sum([Quantity]) by Order ID and Customer. Create a second set from Products, called Must Include. Create a LOD calculation to find those instances where an order contains all products from the Select Products set and also the product from the Must Include set.

{FIXED [ORDER ID]: MAX([SELECT PRODUCTS])} AND {FIXED [ORDER ID]: MAX([MUST INCLUDE])}

For a given Order ID, it will either have products in or out of the sets. If they fall within the set, the calculation will evaluate as true, else false. Use this field as a filter on the views and set to True. Add the filter to context so it happens prior to any of the Fixed LOD calculations. Show both sets. This does not mean to bring the sets to the filter shelf. Rather, from the data pane, right click on each set and select Show Set.

This will add a selector to populate the members of the set. Modify the appearance of the set controls to make the Select Products a multi-select dropdown, customized with an Apply Button. Set the Must Include set control as single value dropdown, showing only those values in context.

Add this same calculated field to the BAN to pass the same logic through to KPI values.

Viz in Tooltip

For the Order Bar Graph view, we want to also include a crosstab as a visualization in tooltip showing the details of the Order ID. Create the crosstab showing SUM([Sales]) and SUM([Quantity]) by Product. From the Order Bar Graph, edit the tooltip and insert the sheet for the Viz in Tooltip. Modify the width and height to accommodate the size of the crosstab.

As you hover over a bar, the Order ID will be passed through to the crosstab and will appear in the tooltip.

Create the Dashboard

Bring the BAN and Order Bar Graph to the dashboard. Add the set controls to the dashboard. Notice that as you select a product from Select Products, the views update to include only those orders that contain the selected products. Selecting a product from the Must Include set filters the views to those orders that contained the selected products as well as the must include product.

In Conclusion

This methodology is a very easy, yet powerful, way to find those products on the same order. This analysis allows the user to determine which products they are most likely to also purchase alongside what other products. The practical uses of this are endless. Set Controls, like the ones used here, were introduced into Tableau in version 2020.2 and allow the user to easily select the members of the set for use within calculations. It opens up a world of options where you are not necessarily just filtering on the members of the set, but rather using it conjunction with other fields and/or logic.