A question I’ve seen asked many times in different guises is how to keep an aggregation static. In other words so it is unaffected by filters for comparison purposes in Tableau. 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.
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 stores sales compare against a group average, or how a regions 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.