Can Not Mix Aggregate And Non Aggregate Tableau

When Tableau shows “can not mix aggregate and non aggregate,” your calculation combines aggregated and row-level fields incorrectly. This error stops your worksheet from rendering, but it’s actually a helpful signal. It tells you that Tableau’s engine can’t decide how to compute a mix of summary numbers and per-row details. Fixing it is straightforward once you understand the logic behind the message.

This article walks you through why the error appears, how to resolve it, and how to avoid it in future work. You’ll learn practical steps, real examples, and common pitfalls. By the end, you’ll handle this error like a pro.

What Does “Can Not Mix Aggregate And Non Aggregate Tableau” Mean?

Tableau works with two types of calculations: aggregate and non-aggregate (also called row-level). An aggregate function, like SUM or AVG, combines multiple rows into a single value. A non-aggregate function, like [Sales] or [Profit], operates on each row individually.

The error appears when you try to combine these two types in a single calculation. For example, writing SUM([Sales]) - [Cost] mixes an aggregate (SUM) with a row-level field ([Cost]). Tableau doesn’t know whether to compute the aggregate first or apply the row-level logic.

This confusion leads to the error message: “Can not mix aggregate and non-aggregate arguments with this function.” The solution involves aligning the aggregation level of all parts of your calculation.

This exact phrase appears in Tableau’s error dialog. It’s a clear indicator that your formula needs restructuring. Let’s break down the most common scenarios and fixes.

Common Causes Of The Error

Using an aggregate function like SUM or AVG alongside a raw field name in the same expression.
Applying a table calculation (which is aggregate) to a field that’s already aggregated incorrectly.
Mixing aggregated and non-aggregated fields in a filter or a calculated field used on a shelf.
Using an IF statement where one branch returns an aggregate and another returns a row-level value.

Each cause has a specific fix. The key is to make every part of your calculation either all aggregate or all row-level.

How To Fix The Error Step By Step

Identify the mixed field: Look at your calculated field or the formula on the shelf. Find where an aggregate function meets a raw field.
Decide on the aggregation level: Do you want the result to be aggregated (like a sum or average) or row-level (like a per-row ratio)?
Wrap the non-aggregate field: If you need an aggregate result, wrap the row-level field in an aggregate function. For example, change SUM([Sales]) - [Cost] to SUM([Sales]) - SUM([Cost]).
Use ATTR() for mixed contexts: If you must keep a field non-aggregated, use ATTR() to make it behave as an aggregate when needed. This works for dimensions.
Create separate calculated fields: Sometimes it’s cleaner to create one field for the aggregate part and another for the row-level part, then combine them on the view.

Let’s look at a real example. Suppose you have a calculated field: SUM([Sales]) / [Quantity]. This mixes aggregate and non-aggregate. To fix it, change it to SUM([Sales]) / SUM([Quantity]). Now both parts are aggregated.

Using FIXED LOD To Bypass The Error

Level of Detail (LOD) expressions can help you mix aggregate and non-aggregate logic without triggering the error. The FIXED LOD computes an aggregate at a specified level, which you can then use with row-level fields.

For example, if you want to compare each row’s sales to the overall average, you can write: [Sales] / {FIXED : AVG([Sales])}. The FIXED part is aggregated, but the overall expression is row-level because [Sales] is not wrapped in an aggregate function.

This technique works because the LOD expression is computed first, then used as a constant in the row-level calculation. Tableau sees it as a valid mix.

Common Mistakes When Fixing The Error

Wrapping the wrong field: Make sure you apply the aggregate function to the non-aggregate part, not the already aggregated one.
Forgetting to aggregate both sides: In a formula like SUM([Sales]) - [Cost] + [Tax], you need to aggregate both [Cost] and [Tax].
Using AGG() incorrectly: AGG() is a table calculation function, not a way to fix this error. Use SUM, AVG, MIN, MAX, etc.
Ignoring the context of the shelf: The error can also appear when you drag a calculated field to a shelf that expects a specific aggregation level.

Double-check your formula syntax. A small typo can cause the error even if the logic is correct.

Advanced Techniques For Complex Calculations

Sometimes the error appears in more complex scenarios, like nested calculations or multi-level aggregations. Here are advanced strategies.

Using Table Calculations To Avoid The Error

Table calculations compute results based on the data in the view. They are inherently aggregate. If you need to mix them with row-level fields, use LOOKUP() or WINDOW functions carefully.

For instance, WINDOW_SUM(SUM([Sales])) - [Sales] will cause the error because WINDOW_SUM is aggregate and [Sales] is row-level. To fix it, wrap [Sales] in an aggregate like SUM: WINDOW_SUM(SUM([Sales])) - SUM([Sales]).

Remember that table calculations work on the aggregated results of the view. They don’t operate on raw data.

Using Parameters To Control Aggregation

Parameters can help you switch between aggregate and row-level logic without rewriting formulas. Create a parameter with options like “Aggregate” and “Row-Level”. Then use a CASE or IF statement to apply different calculations.

Example: IF [Parameter] = 'Aggregate' THEN SUM([Sales]) ELSE [Sales] END. This avoids the error because both branches return the same type? Actually, this will cause the error because one branch returns an aggregate and the other returns a row-level value. To fix it, ensure both branches return the same type. Use ATTR() or wrap the row-level branch in an aggregate.

A better approach: IF [Parameter] = 'Aggregate' THEN SUM([Sales]) ELSE SUM([Sales]) END — but then both are the same. Alternatively, use two separate calculated fields and swap them on the shelf.

Using RAWSQL Functions

RAWSQL functions let you pass SQL directly to the data source. They can bypass Tableau’s aggregation rules, but they come with risks. Use them only when other methods fail.

Example: RAWSQLAGG("SUM(%1) - %2", [Sales], [Cost]). This tells the database to handle the mix. But it may break if the data source doesn’t support it.

RAWSQL functions are not portable across data sources. Test thoroughly before deploying.

Best Practices To Avoid The Error

Prevention is better than fixing. Follow these practices to keep your calculations clean.

Plan your aggregation level before writing formulas. Decide if the result should be per-row or summarized.
Use consistent aggregation across all fields in a calculation. If one field uses SUM, all should use SUM or another aggregate.
Leverage LOD expressions for complex mixes. They give you fine control over aggregation levels.
Test your calculated fields on a simple view before adding filters or table calculations.
Keep formulas short and modular. Break long calculations into smaller, testable parts.

Also, document your calculations. When you return to a workbook months later, you’ll remember why you used a FIXED LOD instead of a simple aggregate.

Real-World Examples And Solutions

Let’s examine three common scenarios where the error appears and how to solve them.

Example 1: Profit Margin Calculation

You want to calculate profit margin: ([Sales] - [Cost]) / [Sales]. This is row-level and works fine. But if you try to aggregate it: SUM([Sales] - [Cost]) / SUM([Sales]), you might get the error if you mix levels.

Solution: Use SUM([Sales] - [Cost]) / SUM([Sales]) — this is valid because both parts are aggregated. Alternatively, compute the margin per row first, then aggregate: SUM([Profit Margin]) where [Profit Margin] is a row-level calculated field.

Example 2: Year-Over-Year Comparison

You want to compare this year’s sales to last year’s. You write: SUM([Sales]) - LOOKUP(SUM([Sales]), -1). This works because both parts are aggregate. But if you try: SUM([Sales]) - [Sales], you get the error.

Solution: Always use aggregate functions with table calculations. Wrap the row-level field in SUM or another aggregate.

Example 3: Conditional Aggregation

You want to sum sales only for a specific category: IF [Category] = 'Furniture' THEN SUM([Sales]) END. This mixes row-level ([Category]) with aggregate (SUM).

Solution: Use SUM(IF [Category] = 'Furniture' THEN [Sales] END). This wraps the entire IF statement inside the aggregate function. Now the IF is evaluated per row, then summed.

FAQ: Can Not Mix Aggregate And Non Aggregate Tableau

Why Does Tableau Show “Can Not Mix Aggregate And Non Aggregate”?

Tableau shows this error when a single calculation combines an aggregate function (like SUM, AVG) with a non-aggregate field (like a raw column name). The engine cannot determine the correct level of detail for the result.

Can I Use ATTR() To Fix The Error?

Yes, ATTR() can help in some cases. It returns the value of a field if it has a single value in the context, otherwise it returns an asterisk. Use it to make a dimension behave as an aggregate when needed.

What Is The Difference Between Aggregate And Non-aggregate In Tableau?

An aggregate function combines multiple rows into one summary value, like SUM or COUNT. A non-aggregate (row-level) field operates on each row individually, like [Sales] or [Customer Name]. Mixing them in one formula causes the error.

How Do I Fix The Error In A Calculated Field?

Identify the mixed parts. Wrap the non-aggregate field in an aggregate function like SUM or AVG. Alternatively, use a LOD expression like FIXED to compute the aggregate separately, then combine with row-level fields.

Can I Avoid The Error By Using Table Calculations?

Table calculations are already aggregate, so they can only be combined with other aggregate expressions. To mix with row-level fields, wrap the row-level field in an aggregate function first.

Final Thoughts On The Error

The “can not mix aggregate and non aggregate” error is one of Tableau’s most common messages. It’s not a bug—it’s a guardrail. It forces you to think clearly about what you want to compute. Once you understand the logic, you can work around it easily.

Practice with simple examples first. Create a calculated field that sums sales, then one that computes a per-row ratio. Gradually add complexity. Over time, you’ll develop an intuition for when and how to mix aggregation levels.

Remember, the key is consistency. If one part of your formula uses an aggregate, all parts should. Use LOD expressions for nuanced control. And when in doubt, break the calculation into smaller pieces.

With these strategies, you’ll never be stuck by this error again. You’ll write cleaner, more efficient Tableau calculations that deliver the insights you need.