What is Table Calculation Filter in Tableau?

Applying a filter in Tableau seems straightforward until your entire view breaks. You filter out a category, and suddenly your "Percent of Total" calculation is wrong, or your moving average chart looks completely off. This common frustration happens because standard filters remove data from your visualization's underlying table, which messes with calculations that depend on that data. This article will show you how to solve this exact problem using a powerful feature called a Table Calculation Filter.

Standard Filters vs. Table Calculation Filters: The Key Difference

To understand why your charts break, you need to know about Tableau's "Order of Operations." Think of it as a checklist Tableau runs through every time it builds a visualization. Standard dimension filters (the ones you probably use every day) are very high up on this list. Table calculations, like Percent of Total or Moving Average, are near the very bottom.

This means when you apply a standard filter, Tableau removes that data before it calculates percentages or moving averages. The calculation is then performed only on the data that's left over, leading to incorrect results.

Let's use a simple analogy. Imagine you baked a dozen cookies (your total data). A standard filter is like deciding before serving that you're going to remove all the chocolate chip cookies from the plate. If you then ask, "What percentage of the cookies on the plate are oatmeal?", you calculate it based on what's left. The total number of cookies has changed.

A Table Calculation Filter works differently. It's the very last step in the Order of Operations. In our analogy, this is like putting all 12 cookies on the plate, figuring out that chocolate chip cookies make up 25% of the total, and then hiding the chocolate chip ones from view. The oatmeal cookies still correctly show their percentage of the original dozen, not just the ones left on the plate.

In short, a standard filter removes data from the calculation, while a table calculation filter simply hides data from the final view, keeping your underlying calculations intact.

When Should You Use a Table Calculation Filter?

This trick is invaluable in several common reporting scenarios where standard filters fall short. Here are the most frequent situations where you'll want to reach for a table calculation filter.

1. Calculating Percent of Total

This is the classic example. Let's say you have a bar chart showing sales for all your product sub-categories, and you've added a "Percent of Total" table calculation. Everything adds up to 100%.

Now, your boss asks to see the chart without "Bookcases" and "Tables." If you use a standard filter to exclude them, the remaining bars will re-calculate their percentages based on a new, smaller total. Their individual percentage values will increase, and the new total will still be 100%. This is misleading because they are no longer percentages of the overall company sales.

A table calculation filter lets you hide "Bookcases" and "Tables" while ensuring the percentages for the remaining sub-categories are still calculated based on the true grand total.

2. Showing Rankings (Top N filters)

Imagine you want to see the Top 10 selling products. You create a view, add an INDEX() or RANK() function, and filter for the Top 10. Now, what if you want to look at the Top 10 products within just one region, say, "East"?

If you add a standard Region filter for "East," Tableau first filters the data to only the East region and then finds the Top 10 products from that reduced dataset. But what you might actually want to know is: of the company's overall Top 10 products, how many of them are in the East region and how did they perform there? A table calculation filter preserves the overall Top 10 ranking and then shows you just the ones that exist in your selected region.

3. Using Moving Averages or other period-over-period metrics

Calculations that rely on previous data points, like a 7-day moving average, are easily broken by standard filters. A 7-day moving average on January 7th needs data from January 1st to the 7th to be calculated correctly.

If you apply a standard date filter to only show data starting from January 7th, the moving average calculation can't "see" the data from the previous six days. The calculation for that first day will be wrong, as it's only averaging one day of data instead of seven. A table calculation filter will hide the visuals for January 1st-6th but keep their data available for the moving average calculation, giving you the correct value for January 7th and beyond.

Step-by-Step Guide: Creating a Table Calculation Filter

Let's build a solution for the most common scenario: correctly filtering a view that uses a "Percent of Total" calculation. We'll use the Sample Superstore dataset that comes with Tableau.

Step 1: Build the Initial View

First, create the basic chart that shows the problem.

• Drag Sales to the Columns shelf.
• Drag Sub-Category to the Rows shelf.
• Sort the chart descending to see the highest-selling sub-categories at the top.
• Right-click the SUM(Sales) pill on the Columns shelf, select Quick Table Calculation, and then choose Percent of Total.

Your view should now show each sub-category's percentage of total sales. If you were to drag the Sub-Category field to the Filters shelf and uncheck a few items, notice how the percentages of the remaining bars increase. This is the behavior we want to fix.

Remove the standard Sub-Category filter before proceeding to the next step.

Step 2: Create the Calculated Field for the Filter

This is where the magic happens. We need to create a new calculated field that uses a table calculation function. This forces Tableau to apply it later in the order of operations.

The LOOKUP() function is perfect for this. LOOKUP() returns the value of an expression in a target row, specified as an offset from the current row.

• Click the down arrow in the Data pane and select Create Calculated Field.
• Name the field something intuitive, like "Sub-Category Filter".
• Enter the following formula:

LOOKUP(MIN([Sub-Category]), 0)

Let's break down this formula:

• [Sub-Category]: This is the dimension we want to filter on. We wrap it in an aggregation like MIN(), MAX(), or ATTR() because table calculations require aggregate measures. In this case, MIN(), MAX(), and ATTR() will all return the same value since we're looking at a single row for each sub-category.
• LOOKUP(..., 0): This part makes it a table calculation. The 0 offset tells Tableau to "look up" the Sub-Category value in the current row. Essentially, it just returns the sub-category's name, but by wrapping it in LOOKUP(), we've changed when Tableau evaluates it.
• Click OK to save the calculated field.

Step 3: Apply the New Calculated Field as a Filter

Now, you'll use this new "Sub-Category Filter" field instead of the original dimension.

• Find your new Sub-Category Filter field in the Data pane.
• Drag it onto the Filters shelf.
• A filter dialog box will appear, listing all the sub-categories. You can select the ones you want to include or exclude. For now, just click OK to add the filter.

Step 4: Show the Filter and See the Results

To make the filter interactive, right-click the "Sub-Category Filter" pill on the Filters shelf and select Show Filter.

Now, try unchecking a few sub-categories from the filter control on the right side of your screen. You will see those bars disappear from the view, but the percentages on the remaining bars will not change. They are still correctly calculated as a percentage of the grand total of all sales. You've successfully filtered the view without breaking your calculation!

Final Thoughts

Understanding the difference between how Tableau removes versus hides data is a massive step forward in your analytics skills. A standard dimension filter removes data early, altering your dataset, while a table calculation filter simply hides rows late in the visual process, preserving the integrity of calculations like Percent of Total or Top N.

We know that mastering nuances like Tableau's Order of Operations takes a lot of time and practice. Learning the purpose of specific functions like LOOKUP() and INDEX() isn't always straightforward, and it can be a barrier to getting quick answers from your data. Here at Graphed, we've designed a platform that handles this complexity for you. By connecting your data sources and asking questions in plain English - like "show me the sales contribution of each product category as a percentage of total sales for last quarter" - you can get accurate, interactive visualizations in seconds. There's no need to learn the procedural steps of specific tools because Graphed automatically understands the logic needed to build the report for you.