skip to content

In Tableau, how does an aggregate filter on SUM(Sales) differ from a dimension filter?

level: middleimportance: should knowfreq 52%

answer

  1. one runs before summing, one after
  2. rows leave versus marks leave
  3. think WHERE and HAVING
  4. the grain of the view decides
  5. adding a field changes what it removes

basics

~20 s

A dimension filter removes underlying rows before aggregation, like SQL WHERE. An aggregate filter on SUM(Sales) is evaluated after aggregation at the view's level of detail, like HAVING, so it removes marks and its meaning changes when the view's grain changes.

solid answer

~50 s

Dropping a dimension such as `Region` on Tableau's Filters shelf restricts which rows reach the aggregation — it becomes a `WHERE` predicate and runs early in the order of operations. Dropping a measure there and choosing an aggregation, say `SUM(Sales) > 100000`, produces an aggregate filter that Tableau can only evaluate after the `GROUP BY`, so it becomes `HAVING` and runs near the bottom of the ladder. The practical consequences: an aggregate filter removes **marks**, not rows, and the aggregate it tests is computed at the view's current level of detail. Add a dimension to the view and the same filter now tests a finer sum, so different things disappear and totals move. Note that dropping a measure on the shelf also offers **All values** instead of an aggregation, which filters raw row values and behaves like a dimension filter.

code

sql · 5 lines
sql
SELECT region, SUM(sales)
FROM orders
WHERE region IN ('East','West','South')  -- Tableau dimension filter
GROUP BY region
HAVING SUM(sales) > 100000               -- Tableau aggregate filter

go deeper

for a junior

Know that dropping a measure on the Filters shelf asks you how to aggregate it, and that the result filters totals rather than individual rows of data.

for a middle

Explain the WHERE-versus-HAVING split, name the stage each occupies in the order of operations, and show that the aggregate is computed at the view's level of detail.

for a senior

Show the production consequence: aggregate filters prune nothing from the scan and silently change meaning when a dashboard's grain changes, so a drill-down can make a threshold view meaningless.

for a principal

Own where such thresholds live. A business rule like 'active customer means more than ten orders' repeated as an aggregate filter in every workbook drifts; decide whether it belongs in the model instead.

## Two different clauses When you put `Region` on Tableau's Filters shelf and select three regions, Tableau adds a predicate to the query: fewer rows come back, and every aggregate downstream is computed from that smaller set. In SQL terms it is a `WHERE`. When you drag the measure `Sales` onto the Filters shelf, Tableau first asks *how* to filter it. Choose `Sum` and set `> 100000` and you get an aggregate filter. Tableau cannot evaluate that until the rows have been grouped and summed, so it lands after the `GROUP BY` — in SQL terms a `HAVING`. ```sql SELECT region, SUM(sales) FROM orders WHERE region IN ('East','West','South') -- dimension filter GROUP BY region HAVING SUM(sales) > 100000 -- aggregate filter ``` ## Rows versus marks This is the difference that matters in practice. A dimension filter changes the data the view is built from. An aggregate filter changes which of the already-built marks survive. If a region totals $80,000 and your filter is `SUM(Sales) > 100000`, that region's bar disappears — but the rows behind it were still read, still aggregated, and still contribute to anything computed above the filter's rung. ## The grain trap An aggregate filter is evaluated at the view's current level of detail, and the level of detail is whatever dimensions are on the shelves. So `SUM(Sales) > 100000`: - On a view with `Region` on Rows, tests each region's total. A handful of large marks survive. - Add `Category` to Rows, and the same filter now tests each region–category total. Many combinations fall under the threshold, so far more marks disappear, and the grand total drops. Nothing about the filter definition changed. The candidate who does not know this reports that "the filter broke when I added a field", which is a very common support ticket and a very common interview probe. ## All values: the third option When you drop a measure on the Filters shelf, one of the choices is **All values**. That filters the raw, un-aggregated values row by row — `sales > 500` on individual order lines — and behaves like a dimension filter, landing in `WHERE`. It is genuinely different from `Sum` on the same field, and mixing them up produces numbers that look almost right, which is the worst kind of wrong. ## Where it sits on the ladder On Tableau's order of operations, a measure filter runs after context filters, after `FIXED` expressions, after dimension filters and after `INCLUDE`/`EXCLUDE` expressions, and before table calculations. That position tells you two useful things. First, an aggregate filter cannot influence a `FIXED` expression — the expression was already computed. Second, an aggregate filter *does* influence table calculations, because they run below it: a running total computed on the view will run over only the surviving marks. ## Performance A dimension filter is usually cheaper because it reduces what the source scans and returns. An aggregate filter cannot reduce the scan at all — every row must be read and grouped before the predicate can be tested — so a dashboard whose only filters are aggregate ones does full work no matter what the viewer selects. When you have a choice, express the restriction as a dimension filter and reserve aggregate filters for genuinely aggregate conditions such as "customers who bought more than five times". ## When you actually want an aggregate filter "Show only products whose profit ratio is negative." "Show only stores above the target." "Show only customers with more than ten orders." These are statements about a computed total, not about a row, and no dimension filter can express them. That is the legitimate use, and the right follow-up question is always "at what grain?" — because the answer determines what the filter means. ## What interviewers listen for The crisp answer is "WHERE versus HAVING, rows versus marks, and the aggregate is computed at the view's level of detail". A candidate who adds "so it changes meaning when I add a dimension to the view" has clearly hit the bug in production and understood it.

  • Why does an aggregate filter give different results after you add a dimension to Rows?
    Because it is evaluated at the view's level of detail. Adding a dimension makes the aggregation finer, so the same threshold is now tested against smaller sums and more marks fall below it. The filter definition never changed — the grain it is measured at did.
  • Can an aggregate filter make a query cheaper?
    No. The source must read and group every row before the predicate can be evaluated, so nothing is pruned from the scan. Only filters that land in WHERE — dimension, data source, context and extract filters — reduce what is read. Prefer those whenever the condition can be expressed on a row.
  • What does the All values option do when you drop a measure on the Filters shelf?
    It filters raw row-level values instead of an aggregate, so it behaves like a dimension filter and lands in WHERE. Filtering Sales > 500 with All values removes individual order lines; choosing Sum instead removes whole marks. The two produce plausible but different numbers, which makes the confusion expensive.

saying these in an interview costs you the question

  • Thinks a measure filter removes underlying rows
  • Expects an aggregate filter to speed up the query
  • Cannot say the aggregate depends on the view's grain
  • Confuses All values with the Sum aggregation option
  • Says both filter types are applied at the same stage

context

resolving links…