Two approaches to benchmarking with Tableau
I’m a business analytics consultant for Amadeus, a technology and software provider to the travel industry, at the Travel Intelligence department, and I often use Tableau to complete market studies. Tableau’s data visualizations allow our customers, airlines, and travel agencies to understand how they’re ranking against their competition. Visual analytics help us see if they are progressing faster or slower than the market, and as a result, if they’re gaining or losing market shares. Benchmarking is a great way to help users understand the impact of business decisions in data visualizations and we'll discuss how to do that in Tableau.
Benchmarking to communicate data more clearly
Context, such as a market share or benchmark, is one of the most important things I consider when building a dashboard because it fundamentally helps the audience interpret data. For example, when using a revenue benchmark, the viewer of a dashboard can quickly see that a decrease or shift in revenue may not be a cause for concern since a bigger drop (portrayed by the benchmark) was expected.
Tableau allows us to use benchmarks in many ways, but I favor and recommend these two approaches:
- The simple method with a defined benchmark: This method should be used when your benchmark does not ever change. For example, if you want to compare your company results to the entire market.
- The advanced method where the user defines the benchmark: This method should be used when you need to define your own benchmark by choosing multiple members of a segmentation field—an example might be a set of competitors.
The simple method with a defined benchmark
In the simple method, you’ll need to distinguish the company, the country, or the team you are analyzing and all the other members of our segmentation field.
Tableau allows you to distinguish with many different possibilities: Sets, Group, Table Calculation (Percent of Total), but the solution that I found to be the simplest, and with few limitations, is an “If” row level calculation.
In this Viz, “World Happiness,” we can see the simple method where there is a defined benchmark— in this case it is country.
This Viz allows the user select a country with a parameter [Country selected], and then use this parameter in an “If” calculation to isolate the KPI value of the country selected:
This calculated field will be null if the row is not for the member you want to analyse (the country choose thanks to the parameter [Country selected]). Now, this new field can be used to benchmark the KPI of one country against all the countries. In the Viz I use this field to show the country selected KPI with a line versus all the countries, the circles:
The advanced method where the user defines the benchmark
The previous approach works very well when you need to benchmark one country compared to the entire market, but it will not work if the user needs to choose multiple members of the segmentation field (e.g. multiple countries) as a benchmark.
Since we want to let the user select multiple members, a parameter cannot be used because only one selection can be made at a time. Instead, we have to use a filter. And because filters are applied to all marks on a sheet in Tableau, we’ll need to use data blending.
To illustrate the five steps of the advanced method we’ll use this visualization about the new indicators of wealth.
- Duplicate the data source so now we have two data sources:
You should see that in the data section:
The main table that we are going to use for the Benchmark
The secondary data source is used for the selected country (in the Viz the [Country Observed] parameter)
- Create a calculated field in each data source called [Blend]. We are going to use these fields to link the two data sources. This will allow us to filter the secondary data source (the country observed)
In the primary data source use the following formula:
In the secondary data source, create a calculated field using this formula:
This calculation is using a parameter called [Country Observed] to allow the user to select the member observed.
- Make sure that the two data sources are linked with the [Blend] fields, and the fields you use to aggregate your Viz (in this example, [KPI Names] and [Year]). The linked fields have a red chain:
- Add the [Blend] field from the primary data source to the “Marks” shelf. To apply the filter on the secondary data source (the [Country Observed] in the Viz below)
- Add an action to filter the main data source (the benchmark). The target filter should be on “The segmentation field” in the Viz the [Country]. In the Viz, the action is triggered by clicking on the map but you could also use a quick filter.