KPI Swapping with BAN Selectors

The following blog by Brian Moore was originally published on Do Mo(o)re With Data September 27, 2022 and is cross-posted here with permission. Brian is a Tableau Visionary, Tableau Public Ambassador, and a Senior Data Analytics and Viz Consultant for Cleartelligence.

Welcome to another installment of “It Depends”. In this post we’re going to look at two different ways to use BAN’s to swap KPI’s in your dashboard. If you’re not familiar with the term “BANs”, we’re talking about the large summarized numbers, or Big Ass Numbers, that are common in business dashboards.

When I build a KPI dashboard, I like to give my users the ability to dig into each and every one of their key metrics, and the techniques we cover in this post are a great way to provide that kind of flexibility. Here is a really simple example of what we’re talking about.

In the dashboard above, we have 4 BAN’s across the top; Sales, Quantity, Profit, and Profit Ratio. Below that, we have a bar chart by Sub-Category, and a Line Chart showing the current vs previous year. When a user clicks on any of the BANs in the upper section, the bar chart and the line chart will both update to display that metric. A couple of other things that change along with the metric are the dashboard title, the chart titles, and the formatting on all of the labels.

We’re going to cover two different methods for building this type of flexible KPI dashboard. A lot of what we cover is going to be the same, regardless of which method you choose, but there are some pretty big differences in how both the BANs and the Dashboard Actions are constructed in each method.

For this exercise we’re going to use Superstore Data, so you can connect to that source directly in Tableau Desktop. If you would like to follow along in the Sample Workbook, you can download that here.

The Two Methods

Measure Names/Values – In the first method we’re going to use Measure Names and Measure Values to build our BANs. When a user clicks on one of the Measure Values, we will have a dashboard action that passes the Measure Name to a parameter.

Individual BANs – In the second method, we’re going to use separate worksheets for each of one of our BANs. When a user clicks on one of the BANs, we’ll pass a static value that identifies that metric (similar to the Measure Name) to a parameter. With this method, we’ll need a separate dashboard action for each of our BANs.

Method Comparison

So at this point you may be wondering, why would you waste time building out separate worksheets and separate dashboard actions when it can all be done with a single sheet and a single action. Fair question. As you’re probably aware, Measure Names and Measure Values cannot be used in calculated fields, so with the Measure Names/Values method, you are going to be pretty limited in what you can do with your BANs. Let’s take another look at the BANs in the example dashboard from earlier.

Numbers alone aren’t always very helpful. It’s important to have context, something to compare those numbers to. Anytime I put a BAN on a dashboard, I like to add some kind of indicator, like percent to a goal, or growth versus previous periods. Another thing I like to do is to use color to make it very obvious which metric is selected and being displayed in the rest of the dashboard. Neither of these are possible with the first method as they both require calculated fields that reference either the selected measure or the value of that measure.

Unlike some of our other “It Depends” posts, the decision here is pretty easy.

Method 2 does take a little more time to set up, but in my opinion, it’s usually the way to go. Beyond the two decision points above, the second method also provides a lot more flexibility when it comes to formatting. But if you’re looking for something quick and these other considerations aren’t all that important to you or your users, by all means, go with the first one.

Methods in Practice

This section is going to focus only on building the BANs and setting up the dashboard actions. We’ll walk through how to do that with both of the methods first, and then we’ll move onto setting up the rest of the dashboard, since those steps will be the same for both methods.

Before we get started, let’s build out just a couple of quick calculations that we’ll be using in one or both methods.

First, let’s calculate the most recent date in our data source. Often, in real world scenarios, you’ll be able to use TODAY() instead of the most recent date, but since this Superstore Data only goes through the end of 2021, we’re going to calculate the latest date.

Max Date: {FIXED : MAX([Order Date])}