There are often situations where you may want to filter a Tableau dashboard without affecting the totals.
There are many examples where you want to filter the view without impacting the underlying data. For example you want to maintain a rank while also applying a filter.
Perhaps you want a running sum, only displaying the numbers from a certain point but maintaining the correct cumulative starting amount. For example perhaps the running sum should calculate from January but only display from April.
This is all possible, however it means using a table calculation filter, a “late” filter. Paying attention to the Tableau Order of Operations, notice table calculations are one of the final things to happen. Using a table calculation as a filter means the filter happens AFTER the standard aggregations have calculated.
In this post I will run through some examples.
How to keep the rank when filtering in Tableau
This is a common request solved using a table calculation as a Tableau “late” filter.
Using a Sample Superstore data source, rank each City by the sum of sales.
[Sales Rank]: RANK(SUM([Sales]))
Assume you want to filter City. Putting the City field to Filters, as a normal filter, doesn’t work. It resets the rank calculation based on what you have on the worksheet. If filtering for only one City it will rank 1. This is down to the order of operations. The Cities are removed with the filter, then the rank calculates on which cities are remaining.
The solution is the same as before. Use a table calculation as a “late” filter. This time the field is a dimension, meaning LOOKUP is the only viable table calculation option. Create a field to use as a City filter. Here, I call that filter field CityLateFilter:
[CityLateFilter]: LOOKUP(ATTR([City],0))
Aggregating is forced within a table calculation, hence we use the ATTR.
Choose the start point of a running sum and ignore filters
Assume we have data for a period of time, for example every month for a year. Perhaps we want to show the running sum of a value, keeping the totals. However maybe users can be enabled to zoom in, starting the visible chart at a later date then the start date. For example if the running sum should begin in January but the chart only display from April. To accurately calculate the April starting point needs the data from Jan to March.
Again, using a Sample Superstore data source, I’ll put together a simple line chart showing cumulative sales. This is the running sum quick table calculation.
Put a filter on Month and choose all months from April onwards. The running sum starts from April, the numbers of Jan to March are filtered out. Therefore we need the running sum to calculate BEFORE the months are filtered.
This time we create a Tableau “late” filter on the month. Keeping things simple for this example (things can get a little more complex building date table calculation filters due to the date parts) I’ll use LOOKUP again. This time it’s a Lookup on the month.
The MONTH function returns an integer value for each month. To match the functionality of a standard Tableau discrete month filter use the DATENAME function, which returns the month name as a string. The “late” filter, which I name [MonthLateFilter], formula is:
[MonthLateFilter]: LOOKUP(ATTR(DATENAME('month',[Order Date])),0)
It doesn’t matter which months are actually on display the cumulative sales numbers remain the same.
This functionality can come in very useful when, for example, reporting on the current quarter and wanting the starting point to be the end of the previous quarter. If it’s Q3 and you want the Q3 start point to the end Q2 end point.
How to Apply Tableau Filter without changing the totals
In this example the goal is to find all companies with a count of employees in 2010 and to compare the evolution of this count over the years before and after 2010. In other words if a company had X or more employees in 2010 display it AND also display the data of other years. This is hard to explain, the below workings should make things clearer.
The data set is fictitious, it contain only 3 fields, Company, Year and Employees.
Initially this seems simple. Calculate the employee count for 2010 then filter that field. To begin create the 2010 employee count calculated field. Name the field [2010 Employee Count]:
[2010 Employee Count]: SUM(IF [Year] = 2010 THEN [Employees] END)
Next create a visualisation to test if it works.
Put the [Year] on Columns, SUM([Employees]) on Rows and [Company] on the Colour shelf. This creates a line graph with annual employee counts over time per company.
Next is to filter this only keeping companies with a 2010 employee count of X (you choose). Put [2010 Employee Count] on filters and use the At Least filter to show all companies with over X employees in 2010. Notice it doesn’t work, and it’s clear why not when digging into the detail.
The filter affects all years, not only 2010. Looking at the calculation, for all years that are not 2010, it returns Null. We are filtering out all years apart from 2010 BEFORE we have the chance to calculate the totals of those other years.
We actually need a filter to affect only 2010, and not impact the totals of the other years.
Remember the Tableau order of operations, where table calculations are one of the last things to evaluate? If the filter field is a table calculation, not a standard measure calculation, it should only apply the filter after all yearly totals have finished calculating. Therefore change the formula of [2010 Employee Count] to use a table calculation. As we have to calculate the total of 2010 to act as the table calculation filter, this means we use a “summing” table calculation. The summing table calculation options are WINDOW_SUM and TOTAL.
Using a table calculation filter in Tableau
Using WINDOW_SUM changes the formula to:
[2010 Employee Count]: WINDOW_SUM(SUM(IF [Year] = 2010 THEN [Employees] END))
Applying this as a filter should now work. It shows all years for all companies with the selected amount of employees in 2010.
To further improve can give the user a better experience we can alter this slightly. Create an integer parameter to allow the user to select the minimum number of employees. Then create another calculated field using the parameter value and the 2010 employee count returned by the calculated field.
[2010EmployeeFilter]: [2010EmployeeCount] = [MinEmployees2010]
Put this into the filter shelf, replacing the [2010 Employee Count] and set to True.
As it’s using a table calculation in the filter this evaluates almost last, creating a Tableau “late” filter.
Tableau late filters also have limitations
An important thing to keep in mind, table calculation filters only affect a single worksheet. Unfortunately, it’s not possible for these “late” filters to affect multiple worksheets on your dashboard.