A question I’ve seen asked many times in different guises is how to keep an aggregation static in Tableau.
In other words, so it is unaffected by filters for comparison purposes in Tableau.
2017 Update – this, FIXED Level of Detail calculations made what was once complicated, and explained below, very straightforward. The method still contains value as data source duplication to “lock” a number has uses on occasions where a FIXED calculation doesn’t meet requirements.
The solution is dependent on the circumstances of the data structure and how the visualisation is filtered.
Unfortunately I can’t be clearer than that.
As everybody who’s familiar with Tableau knows, every solution is different, and there are often multiple ways to reach the same goal.
How to compare a number in Tableau against an aggregate group of numbers
In this post, I’m going to detail the simplest way to do a comparison of individual values against an aggregated value.
For example, how an individual store’s sales compare against a group average, or how a region’s new business leads compare against the entire country.
The key to this is to have the aggregation not affected by the filtering to allow for comparison.
This example is using very simple data – I have 2 regions, the sales and the month. I want to compare the individual regions against the average of both regions.
Create the line chart
Step 1. Create the visualisation with average by region. Put the AVG(sales) on the row shelf, Month to the columns shelf and region on the Colour shelf. By default this gives a line chart with average sales trended by month.
Step 2. Next duplicate the data source. To duplicate a data source right click on the data source in the top left and Duplicate. We use the duplicate for the average of all regions and, since it’s in a different data source, it won’t be affected by filtering. One of the characteristics of data blending is filtering doesn’t always affect both data sources. In this case that’s a positive, we use it to our advantage.
Step 3. From the duplicate data source drag the Sales field to the rows shelf, alongside the initial Avg(sales) pill. Note the orange check mark indicates it’s from the secondary data source. Your workbook should now look like this:
Step 4. Next convert to a dual axis chart. Click on the AVG([Sales]) with the orange check mark and select dual axis. Synchronise the axes.
Step 5. Almost there. Now remove the Region pill from the Colour shelf of the region average. To the left where it says Marks click the little down arrow and select Multiple Mark Types.
Step 6. Scroll through the mark types until you find the avg(sales) with the orange tick mark alongside. Remove the Region field from this colour shelf.
Step 7. Now you’ve done it! To test convert the region from the initial data source (no orange tick mark) to a quickfilter. Notice the region average remains static even when applying the filter.