How to Use BANs as KPI Selectors in Tableau

Tableau has some incredibly powerful tools for interacting with your data. This 4-part series contains a number of tips and tricks for leveraging those tools to build business dashboards that are more flexible, more engaging, more powerful, and generally, just a lot more fun to use. This is part 2 of the series and covers how to use BANs (Big Aggregate Numbers) and Dashboard Actions to select different KPIs that appear in your dashboard. It will also cover how to dynamically format your labels and tooltips based on your KPI selection, how to counter Tableau's default highlighting using Filter Actions, and how to sheet swap using Parameter Actions. Check out the Resources below for a link to the dashboard referenced in this blog post, and to a short video demonstrating all of the steps in this post.

Resources:

Superstore KPI Dashboard on Tableau Public

Building Better Dashboards Through Interactivity - Part 2 Video

---

Allowing users to modify your dashboard by selecting different KPI's is a great way to add flexibility to your dashboard. With this method, a single dashboard can support users from different departments and from different levels of the organization. It provides both high level summaries of all of your KPI's, but also has the flexibility to dive deeper into each of those metrics.

Once you have created all of your BANs, which are essentially a simple view with a measure placed on Text, we want to create our Parameter and all of our Metric Calculations

1. Create a calculated field for each of your BANs

   1. For Sales, create a calculated field called "Sales" where both the calculation and the value in the body of the calculation are the word Sales in quotation marks

   2. Repeat for all of your other BANs (ex. Profit, Quantity, and Shipping)

2. In each BAN, drag the appropriate calculated field from the step above to Detail

3. Create a Parameter called "Metric Select", set the Data Type to String and the Current Value = Sales

4. Create a calculated field called "Metric Calc" which will represent whichever measure is selected in the "Metric Select" Parameter

In each of the worksheets in your dashboard you'll want to use the "Metric Calc" measure created above. See the Sample Dashboard for Examples. This measure is placed on columns for all of the bar charts, on rows for the area chart, and on color the map. This way, the entire dashboard adjusts when the Parameter is changed. The next thing we'll want to do is create our Parameter Actions that will allow us to change that Parameter by clicking on our BANs.

1. Click on Dashboard > Actions

2. Click "Add Action"

3. Select "Change Parameter"

4. Give the parameter a name

5. Under Source Sheets, select the 1st BAN worksheet (ex. "BAN - Sales")

6. Choose "Select" under the "Run Action On" options

7. Under "Target Parameter", select the "Metric Select" parameter

8. Under "Field", select the "Sales" field that we created in the steps above

9. Your final parameter action should look like this

Once you've added the Parameter Action for the Sales BAN, you'll repeat it for all of the other BANs (in the Sample Dashboard there would be a total of 4)

Now when we click on any of our BANs we are able to modify the entire dashboard to be able to dive deeper into that KPI. However, you may notice, that when you click on a BAN the entire section gets highlighted in blue. In Part 1 of this series we discussed how to counter Tableau's default highlighting with a Highlight Action, but unfortunately that method is ineffective for Text mark types. Instead, we will use a Filter Action to counter the default highlighting and better control the appearance of the selected and non-selected marks.

1. Go to the "BAN - Sales" worksheet

2. Create a calculated field called 0 with just a 0 in the body of the calculation

3. Create a calculated field called 1 with just a 1 in the body of the calculation

4. Drag both the 0 and 1 fields to Detail

5. Add the Dashboard Action

   1. Click on Dashboard > Actions

   2. Click "Add Action"

   3. Select "Filter"

   4. Give the Action a name

   5. Under Source Sheets select the "BAN - Sales" worksheet

   6. Under Target Sheets click on the drop-down and select the "BAN - Sales" worksheet (do not select it from the default display list)

   7. Choose "Select" under the "Run Action On" options

   8. Under "Clearing Selection Will" select "Show All Values"

   9. Under "Target Filters" choose "Selected Fields"

   10. Click the "Add Filter" button

   11. Under the "Source" Field choose 0

   12. Under the "Target" Field choose 1

   13. Click OK

   14. Your final Filter Action should look like this

Now when we click on a BAN, it will modify the dashboard without the highlight, but it's difficult to determine which BAN is selected. To remedy this, we'll use color in our BANs

1. Go to the "BAN - Sales" worksheet

2. Create a calculated field called "KPI Selected - Sales"

   1. [Metric Select]="Sales"

3. Drag the "KPI Selected - Sales" field to color

4. Update the colors for the True and False values

5. Repeat for all other BANs

We can take this a step further by displaying text that provides more detail on which BAN is selected and instructions on switching metrics.

1. Go to the "BAN - Sales" worksheet

2. Right click on the "KPI Selected - Sales" field and select "Aliases"

3. Alias the True value to display "Viewing Sales"

4. Alias the False value to display "Click to View Sales"

5. Drag the "KPI Selected - Sales" field to Text

6. Click on Text and format your BAN

7. Repeat for all other BANs

One last thing that we can do is add the selected BAN to our worksheet titles so it's clear in each section, which KPI is displayed

1. Double click in your first worksheet title

2. Place the cursor in the text box

3. Click on "Insert" and then select "Parameters.Metric Select"

4. Update the rest of the title (ex. Parameters.Metric Select by Region will display as Sales by Region when Sales is selected

5. Repeat for all other worksheets in the dashboard

---

Now our interactive BANs are complete but there are a couple of potential issues. In each of our views we have a dynamic measure that could be currency (Profit or Sales), a whole number (Quantity) or a percentage (Shipping), but we are only able to select a single format for our Metric Calc measure. Also, an area chart may be a good chart option when viewing Sales, Profit, or Quantity, but might not be a good option when looking at Shipment Pass Rates. In this section we'll cover how to dynamically format your mark labels, and how to use Parameter Actions to swap sheets. We'll start with the dynamic formatting.

1. Create a calculated field for each BAN

   1. For Sales, create a calculated field called "Label - Sales

   2. IF [Metric Select]="Sales" THEN [Metric Calc] END

   3. Repeat for all other BANs, replacing "Sales" with the other Metric options

2. Update default formatting

   1. Right click on the "Label - Sales" field

   2. Click on Default Properties > Number Format

   3. Set the appropriate format for that measure (ex. Currency)

   4. Repeat for all other Label calculated fields

3. Add Label calculations

   1. Go to your first worksheet

   2. Remove any fields currently on Label

   3. Drag all of the Label calculated fields created above to Label

4. Format your Label

   1. Click on "Label" on the Marks card

   2. Click on the space after the field in the first row and hit delete to bring the second field onto the first row

   3. Repeat for the next fields so that all of your Label fields are next to each other, without spaces, on the first row

   4. Repeat for all worksheets in your dashboard

Now, as we click on different BANs the labels in our dashboard will update appropriately based on the selected metric. Next, we'll cover how to swap views in our dashboard using a Parameter Action.

1. Create both of your worksheets (in this example we have an Area chart and a Line chart)

   1. When Sales, Profit, or Quantity are selected we want to display the Area chart. When Shipping is selected, we want to display the Line chart.

2. Add your views to the dashboard

   1. Drag out a horizontal container

   2. Add both of your worksheets to the container

   3. Hide the titles on both of the worksheets (right click on title and select "Hide Title")

3. Create a calculated field "Sheet Swap Filter"

   1. [Metric Select]="Shipping"

4. Filter your views

   1. Go to the line chart (in this example it is named "Metric Over Time - Shipping")

   2. Drag the "Sheet Swap Filter" field to the filter shelf and filter on True (you may need to add this value to the Custom Value List if Shipping isn't currently selected)

   3. Go to the Area Chart (in this example it is named "Metric Over Time")

   4. Drag the "Sheet Swap Filter" field to the filter shelf and filter on False (you may need to add this value to the Custom Value List if Shipping is currently selected)

Now when we click on the Sales, Profit, or Quantity BANs the Area chart will be displayed, and when we click on the Shipping BAN, the line chart will be displayed.

That is it for today. Keep an eye on our blog for Parts 3 & 4 over the coming weeks, which will cover how to create "Scroll" buttons, and how to add an instruction "overlay". If you have any questions or comments on these tips, please let us know at info@cleartelligence.com.