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:
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
Create a calculated field for each of your BANs
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
Repeat for all of your other BANs (ex. Profit, Quantity, and Shipping)
In each BAN, drag the appropriate calculated field from the step above to Detail
Create a Parameter called "Metric Select", set the Data Type to String and the Current Value = Sales
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.
Click on Dashboard > Actions
Click "Add Action"
Select "Change Parameter"
Give the parameter a name
Under Source Sheets, select the 1st BAN worksheet (ex. "BAN - Sales")
Choose "Select" under the "Run Action On" options
Under "Target Parameter", select the "Metric Select" parameter
Under "Field", select the "Sales" field that we created in the steps above
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.
Go to the "BAN - Sales" worksheet
Create a calculated field called 0 with just a 0 in the body of the calculation
Create a calculated field called 1 with just a 1 in the body of the calculation
Drag both the 0 and 1 fields to Detail
Add the Dashboard Action
Click on Dashboard > Actions
Click "Add Action"
Select "Filter"
Give the Action a name
Under Source Sheets select the "BAN - Sales" worksheet
Under Target Sheets click on the drop-down and select the "BAN - Sales" worksheet (do not select it from the default display list)
Choose "Select" under the "Run Action On" options
Under "Clearing Selection Will" select "Show All Values"
Under "Target Filters" choose "Selected Fields"
Click the "Add Filter" button
Under the "Source" Field choose 0
Under the "Target" Field choose 1
Click OK
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
Go to the "BAN - Sales" worksheet
Create a calculated field called "KPI Selected - Sales"
[Metric Select]="Sales"
Drag the "KPI Selected - Sales" field to color
Update the colors for the True and False values
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.
Go to the "BAN - Sales" worksheet
Right click on the "KPI Selected - Sales" field and select "Aliases"
Alias the True value to display "Viewing Sales"
Alias the False value to display "Click to View Sales"
Drag the "KPI Selected - Sales" field to Text
Click on Text and format your BAN
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
Double click in your first worksheet title
Place the cursor in the text box
Click on "Insert" and then select "Parameters.Metric Select"
Update the rest of the title (ex. Parameters.Metric Select by Region will display as Sales by Region when Sales is selected
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.
Create a calculated field for each BAN
For Sales, create a calculated field called "Label - Sales
IF [Metric Select]="Sales" THEN [Metric Calc] END
Repeat for all other BANs, replacing "Sales" with the other Metric options
Update default formatting
Right click on the "Label - Sales" field
Click on Default Properties > Number Format
Set the appropriate format for that measure (ex. Currency)
Repeat for all other Label calculated fields
Add Label calculations
Go to your first worksheet
Remove any fields currently on Label
Drag all of the Label calculated fields created above to Label
Format your Label
Click on "Label" on the Marks card
Click on the space after the field in the first row and hit delete to bring the second field onto the first row
Repeat for the next fields so that all of your Label fields are next to each other, without spaces, on the first row
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.
Create both of your worksheets (in this example we have an Area chart and a Line chart)
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.
Add your views to the dashboard
Drag out a horizontal container
Add both of your worksheets to the container
Hide the titles on both of the worksheets (right click on title and select "Hide Title")
Create a calculated field "Sheet Swap Filter"
[Metric Select]="Shipping"
Filter your views
Go to the line chart (in this example it is named "Metric Over Time - Shipping")
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)
Go to the Area Chart (in this example it is named "Metric Over Time")
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.