Tableau Can't Mix Aggregate and Non-Aggregate Error

It's Monday morning and your sales dashboard shows a red calculation error instead of numbers. The formula looks harmless: IF [Region] = "West" THEN SUM([Sales]) END. You tested each half separately and both worked. Together they explode, and the error message — cannot mix aggregate and non-aggregate arguments — reads like it was written for a compiler, not a human.

This error is Tableau's most common calculation complaint, and it confuses beginners because the formula feels logical. You're testing a row-level fact (this order's region) and returning a summary (total sales). In Excel that pattern works fine. In Tableau it can't, because Tableau builds queries at a specific level of detail and every part of one calculation must agree on that level.

The stakes are real. A broken calculated field turns every sheet that uses it red, and if that field feeds a published dashboard, your Monday audience sees the failure. Worse, the quick fixes people reach for — wrapping everything in ATTR() until the error disappears — can silently return wrong numbers that look right.

By the end of this article you'll read the error as plain English: two granularities collided. You'll know the three legitimate repairs (restructure the IF, wrap the dimension, or write a LOD), you'll understand exactly what ATTR() promises, and you'll have a checklist that keeps this error off dashboards for good.

What Tableau Means by Mixing Aggregate and Non-Aggregate Arguments

Every Tableau calculation executes at one level of detail, and every function in that expression must agree on it. SUM([Sales]) collapses many rows into one number — that's aggregate. Bare [Region] returns one value per row — that's non-aggregate. Combine them in IF [Region] = "West" THEN SUM([Sales]) END and Tableau faces an impossible question: should it evaluate per row or per view? It refuses to guess, and the error is that refusal.

Think of pills on the shelf as a contract. Dimensions slice the view into partitions; measures aggregate within each partition. A calculated field inherits this contract. When your formula asks for a row's region and the partition's total in the same breath, no single SQL GROUP BY can satisfy both, so compilation fails before any data is touched.

Beginners trip here because each half works alone. [Region] = "West" is a valid boolean. SUM([Sales]) is a valid number. The error only appears at combination time, which feels like Tableau is being stubborn. It's actually protecting you: a silent guess about granularity would produce numbers that look right and aren't.

The mental model that ends the confusion is simple. Label every piece of a new calc as row or aggregate before you write it. If both labels appear, stop — you need one of the three repairs in this article. Once labeling becomes habit, you'll catch the error in your head before Tableau catches it on screen.

This matters beyond one error dialog. Granularity discipline is the same skill behind correct LOD expressions, trustworthy table calcs, and totals that reconcile. Master it here and every advanced Tableau topic gets easier.

// BROKEN: row-level test + aggregated result in one expression
IF [Region] = "West" THEN SUM([Sales]) END
// Error: cannot mix aggregate and non-aggregate arguments

// FIXED: classify each row first, then aggregate
SUM(IF [Region] = "West" THEN [Sales] END)

// FIXED with ELSE branch kept explicit
SUM(IF [Region] = "West" THEN [Sales] ELSE 0 END)

The IF Trap: Row-Level Tests Wrapped Around SUM Totals

The IF function is where most analysts meet this error, because IF invites you to test a dimension and return a measure. IF [Region] = "West" THEN SUM([Sales]) END reads like English, which is exactly why it's wrong: the condition lives at row level while the result lives at view level, and Tableau cannot evaluate both levels in one pass.

The repair reverses the nesting. SUM(IF [Region] = "West" THEN [Sales] END) tests every row first — each order is West or it isn't — and then sums the survivors. The IF now operates purely at row level, and SUM aggregates the outcome. Same business logic, one consistent granularity, no error.

Date comparisons follow the same rule. IF [Order Date] >= #2024-01-01# THEN SUM([Sales]) END fails identically, and the fix is SUM(IF [Order Date] >= #2024-01-01# THEN [Sales] END). Any time your condition references a raw field and your result aggregates, push the aggregation outside.

ELSE branches deserve attention too. Omitting ELSE leaves NULL for non-matching rows, and SUM ignores NULLs — usually what you want. Writing ELSE 0 instead includes zeros, which changes AVG but not SUM. Pick deliberately: for averages over matched rows only, leave ELSE out; for averages over all rows, write ELSE 0.

Nested IFs multiply the risk. Each branch must independently respect the granularity contract. When nesting gets deep, split the row-level classification into its own boolean field — [Is West] as [Region] = "West" — then aggregate cleanly with SUM(IF [Is West] THEN [Sales] END). Two simple fields beat one clever one.

// BROKEN: date test at row level, result aggregated
IF [Order Date] >= #2024-01-01# THEN SUM([Sales]) END

// FIXED: test inside, aggregate outside
SUM(IF [Order Date] >= #2024-01-01# THEN [Sales] END)

// CLEANEST: split classification from aggregation
// [Is West]  :=  [Region] = "West"
SUM(IF [Is West] THEN [Sales] END)
AVG(IF [Is West] THEN [Profit] END)  // NULLs excluded from average

ATTR() Under the Microscope: Fix, Workaround, or Silent Lie

ATTR() is Tableau's special aggregation: if all rows in a partition share one value, it returns that value; if they differ, it returns '*'. That contract makes IF ATTR([Region]) = "West" THEN SUM([Sales]) END compile — both sides are now aggregate. But compiling and being correct are different things, and ATTR() punishes blind trust.

ATTR() tells the truth at detail levels where one value genuinely exists per partition. A view sliced by Order ID with ATTR([Region]) is safe: each order has exactly one region. The same calc at a yearly total mixes regions, returns '*', and the IF comparison against "West" fails — the row quietly vanishes from your result instead of erroring loudly.

That silent drop is the production hazard. The incident in this article lost 18% of bonus sales for a week because '*' rows excluded themselves without any warning. The error dialog is annoying but honest; an ATTR() patch that compiles can be wrong and quiet, which is far more expensive.

Use ATTR() as a diagnostic, not a default. When it returns '*', treat that as information: your partition genuinely holds multiple values, and you must decide what the business wants — filter to one, sum conditionally, or compute per-value with a LOD. The asterisk is Tableau asking you a question. Answer it instead of hiding it.

Reserve ATTR() for display-only labels you have verified single-valued, like showing a customer's segment next to their LOD total. For anything that feeds money math, prefer MIN()/MAX() with a documented guarantee or a conditional SUM that can't produce '*'.

// COMPILES but dangerous at mixed totals
IF ATTR([Region]) = "West" THEN SUM([Sales]) END
// At a total mixing regions, ATTR() = "*" and the row drops out

// SAFE: conditional aggregation never produces "*"
SUM(IF [Region] = "West" THEN [Sales] END)

// LEGITIMATE ATTR() use: single value guaranteed per partition
// View sliced by [Order ID], one region per order
ATTR([Region])

MIN() and MAX() Wrappers: The Safer Way to Satisfy the Compiler

When a dimension genuinely holds one value per partition, MIN() and MAX() convert it to an aggregate without ATTR()'s asterisk behavior. IF MIN([Region]) = "West" THEN SUM([Sales]) END compiles, and at mixed totals it returns an actual region instead of ''. That determinism makes failures visible rather than silent — a wrong-but-visible value gets caught, while '' rows just disappear.

Dates benefit most from this pattern. IF MIN([Order Date]) >= #2024-01-01# THEN SUM([Sales]) END tests the earliest date in each partition, which is meaningful for cohort logic like 'customers whose first order was this year.' ATTR([Order Date]) in the same spot would star out on any partition with two dates, killing the comparison.

The wrapper must be semantically honest. MIN([Region]) on a partition with both West and East returns East alphabetically — deterministic but arbitrary. If your partitions can mix, the wrapper compiles yet the business logic is undefined. Only wrap when the view's granularity guarantees single values, such as Order ID determining Region functionally.

A useful test: add COUNTD([Field]) beside your calc. If it ever exceeds 1 in a partition your calc touches, your MIN()/MAX() wrapper is hiding variation that the business should decide about. Either filter, split the view, or switch to conditional aggregation.

Compared with ATTR(), MIN()/MAX() fail loudly and sort predictably, which is why experienced developers prefer them. Neither replaces restructuring the calc when restructuring is possible — but when a wrapper is truly warranted, MIN() or MAX() with a COUNTD guard is the professional choice.

// Deterministic wrapper: earliest date per partition
IF MIN([Order Date]) >= #2024-01-01# THEN SUM([Sales]) END

// Guard query: COUNTD above 1 means the wrapper hides variation
COUNTD([Region])

// Same pattern in raw SQL: bare column must be grouped or aggregated
SELECT Region, SUM(Sales) AS TotalSales
FROM Orders
GROUP BY Region;

The LOD Escape Hatch: FIXED Calculations That End the Fight

Sometimes you genuinely need two levels in one view: each row's sales beside its customer's total. No single-granularity calc can do that — which is exactly what Level of Detail expressions solve. { FIXED [Customer ID] : SUM([Sales]) } computes the total at customer level regardless of view layout, then lets each row compare itself against it.

The classic ratio calc shows the power: SUM([Sales]) / SUM({ FIXED [Customer ID] : SUM([Sales]) }) gives each row's share of its customer total. The numerator follows the view; the denominator is pinned. Both are aggregate, so they combine cleanly, yet they embody different levels. That's the escape hatch the raw error denies you.

FIXED ignores dimension filters unless they're added to context, which is the sharp edge. A regional filter won't shrink a FIXED total by default — your numerator filters but your denominator doesn't, and shares exceed 100%. Right-click the filter, choose Add to Context, and the LOD respects it. Test every LOD calc with its filters on and off before publishing.

INCLUDE and EXCLUDE tune rather than pin. { INCLUDE [Segment] : SUM([Sales]) } adds granularity finer than the view; { EXCLUDE [Region] : SUM([Sales]) } computes coarser than the view. Reach for them when FIXED feels too rigid, but default to FIXED for totals that must survive any layout change.

LOD expressions cost queries, so use them where the business question demands mixed granularity — not as a reflex for every mismatch error. Restructure first, wrap second, LOD third. When you do need LOD, it's the correct tool, not a workaround.

// Per-customer total independent of view layout
{ FIXED [Customer ID] : SUM([Sales]) }

// Each row's share of its customer total
SUM([Sales]) / SUM({ FIXED [Customer ID] : SUM([Sales]) })

// Grand total pinned for percent-of-total views
{ FIXED : SUM([Sales]) }

A Prevention Checklist So This Error Never Reaches Your Dashboard

Prevention beats repair, and this error is highly preventable with three habits. First, label granularity before writing: mark each field row or aggregate in a comment line above the calc. If both labels appear, you already know a restructure, wrapper, or LOD is required — no surprise dialog needed.

Second, build conditional logic in two fields. A boolean classification field ([Is West], [Is Recent]) holds the row-level test and can be unit-checked with row counts. The aggregation field (SUM(IF [Is West] THEN [Sales] END)) holds the math. Reviewers validate each half independently, and future editors can't accidentally reintroduce the mix.

Third, maintain a scratch validation sheet in every workbook. It shows COUNTD guards beside wrapped dimensions, conditional SUMs beside ATTR() versions, and LOD totals beside table-calc replications. When all three agree, publish. When they diverge, you've caught the next incident in development instead of in front of leadership.

Extend the discipline to totals and subtotals. Every new calc gets a totals glance: does the total equal the sum of visible rows? ATTR()-based calcs fail this test by design at mixed levels, which is your cue to replace them with conditional aggregation before anyone relies on the number.

Finally, document the exceptions. When a MIN() wrapper or FIXED LOD is genuinely correct, say why in a comment: which functional dependency guarantees single values, which filters sit in context. The next analyst inherits your reasoning, not just your formula — and that's what keeps dashboards correct after you've moved on.