How to Add Aggregate and Non-Aggregate in Tableau

Trying to mix aggregated and non-aggregated calculations in a Tableau formula is a rite of passage for anyone learning the tool. You write a seemingly simple calculation, but instead of a beautiful new chart, Tableau presents you with the dreaded error: "Cannot mix aggregate and non-aggregate arguments."

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

This isn't a bug, it's Tableau's way of telling you that you're trying to compare data at two different levels of detail, like trying to add the total height of a basketball team to the shoe size of a single player. This article will explain exactly what aggregate and non-aggregate fields are, why they can’t be mixed, and several practical ways to fix the issue and get the insights you need.

What Are Aggregate and Non-Aggregate Fields?

To solve the problem, you first need to understand the two different types of data you’re working with in Tableau. It all comes down to the level of detail.

Non-Aggregate Data (Row-Level)

Non-aggregate data refers to the values in each individual row of your dataset. Think of it as your raw data. If you have a spreadsheet of sales transactions, each row might represent a single product sold in an order. Columns like Product Name, Sales, Quantity, and Profit are all non-aggregate at this stage.

A calculation is non-aggregate if it’s performed for every single row in your data source. For example, if you wanted to calculate a "Profit per Item" in a dataset where each row is a line item, the formula would be:

[Profit] / [Quantity]

Tableau would calculate this for every row independently. This is a non-aggregate, or row-level, calculation.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Aggregate Data (View-Level)

Aggregate data is the result of a calculation performed on a set of rows. When you drag a measure like 'Sales' into your Tableau view, it automatically gets wrapped in an aggregation, usually SUM(). Instead of showing you the sales figure for every single row, it shows you the total of all sales for the visible dimensions on your chart (like by year or region).

Common aggregations include:

• SUM(): Adds all the numbers in a set together.
• AVG(): Calculates the average of the numbers.
• MIN(): Finds the smallest value.
• MAX(): Finds the largest value.
• COUNT(): Counts the number of rows.
• COUNTD(): Counts the number of unique or distinct values.

An aggregate calculation operates on these grouped results. For instance, calculating the overall profit ratio for a category would be:

SUM([Profit]) / SUM([Sales])

This formula first calculates the total profit for the category, then calculates the total sales for the category, and finally divides one by the other. It doesn’t look at individual rows.

Why Can't You Mix Aggregate and Non-Aggregate?

The infamous error "Cannot mix aggregate and non-aggregate arguments..." appears when you try to use both types of data in the same formula. Tableau throws this error because the logic is impossible to resolve. The levels of detail are incompatible.

Consider this hypothetical formula, where you want to compare the sales of a single transaction to the average of all sales:

[Sales] > AVG([Sales])

Here’s the problem:

• [Sales] is non-aggregate. It refers to the value in the 'Sales' column for a single row.
• AVG([Sales]) is an aggregate. It refers to a single value: the average of all sales rows being considered in the view.

Tableau doesn't know how to compare the value from one specific row (which row?) to the computed average of many rows. The request is ambiguous and doesn’t have a logical answer.

How to Fix the "Mix" Error in Tableau

Fortunately, there are a few straightforward ways to solve this. The core strategy is to make sure all components of your calculation exist at the same level of detail, either by aggregating everything or by using special functions to bring an aggregated value down to the row level.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Solution 1: Aggregate the Non-Aggregate Field

The most common and simplest solution is to wrap the non-aggregate part of your formula in an aggregation. This tells Tableau exactly how to compute the value for that field so it can be compared with the other aggregated part.

Let’s say you want to identify days where you had more than $10,000 in sales. Your problematic formula might be:

Incorrect Logic: Find individual sales transactions greater than $10,000.

IF [Sales] > 10000 THEN "High Value Transaction" END

What you actually want to know is which days had a total sales over $10,000. So you need to operate on the sum of sales per day.

Correct Logic: Find days where the sum of sales is greater than $10,000.

IF SUM([Sales]) > 10000 THEN "Good Day" ELSE "Okay Day" END

Here, by wrapping [Sales] in SUM(), you’ve made it an aggregate calculation. When you drag this onto your report with 'Order Date' (set to Day), Tableau will calculate the sum of sales for each day and correctly apply your label.

You can use any aggregation here (SUM, AVG, MIN, MAX). Just choose the one that matches the business question you're asking.

Solution 2: Use an Attribute (ATTR) Function

Sometimes, your field is technically non-aggregate but only has one possible value within the context of your view. For example, if you have Category in your view rows and want to reference another dimension that is the same for every row within that category.

In this case, you can use the ATTR() (Attribute) function. ATTR() checks if all rows in the group have the same value. If they do, it returns that value, otherwise, it returns an asterisk (*).

IF ATTR([Category]) = "Technology" THEN SUM([Sales]) / 2 ELSE SUM([Sales]) END

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Solution 3: Use a Level of Detail (LOD) Expression

LOD expressions are Tableau's superpower for handling mismatched levels of detail. They allow you to compute an aggregated value at a level of detail different from what's in your view, save it, and then use it as a non-aggregate (row-level) value.

The most common LOD for fixing this issue is FIXED.

Let's say you want to segment customers into "VIP" and "Regular" based on their total lifetime sales. You want this VIP tag to apply to every single transaction that customer has ever made.

The challenge is that SUM(Sales) is an aggregate, but Customer Name is a dimension. Attempting something like this will throw the error:

// This will cause an error
IF SUM([Sales]) > 5000 THEN "VIP"
ELSEIF [Region] = "East" // Error: Can't mix aggregate and non-aggregate
THEN "Regular" END

You can solve this with a FIXED LOD expression:

Step 1: Create a calculated field to find total sales per customer.

{ FIXED [Customer Name] : SUM([Sales]) }

This code calculates the sum of sales for each customer name and "fixes" it. The result is a new value that gets virtually stamped onto every row of data. Now, every single transaction for "John Smith" will have a Total Sales per Customer value equal to John's lifetime spending.

Step 2: Use the new row-level field in your logic.

Now you can create your customer segment calculation without any mixing errors:

IF [Total Sales per Customer] > 5000 THEN "VIP" ELSE "Regular" END

This works because both [Total Sales per Customer] and any other dimension you might check (like [Region]) are now on the same non-aggregate level.

When should you use which solution?

• Aggregation (SUM, AVG, etc.): Use this when you're creating a simple calculation that directly relies on the dimensions in your view. It's the most common and efficient solution for creating summary KPIs.
• LOD Expression (FIXED): Use this when you need to use a calculated aggregate as a dimension or in a later row-level calculation. It's for when you need to answer a question at a different grain than your visualization, like identifying a customer's status based on their entire history.

Final Thoughts

Understanding the difference between aggregate and non-aggregate fields is one of the most important concepts to master in Tableau. At its core, the issue is about levels of detail. Once you learn to spot mismatched levels and use tools like aggregation or Level of Detail expressions to align them, you'll find yourself building more powerful and flexible reports without getting stuck on errors.

As you've seen, mastering these rules often requires learning specific functions and ways of thinking about data structure. For many teams, this can be a hurdle. At Graphed, we handle all this complexity behind the scenes. Instead of writing LOD expressions, you can simply ask questions in plain English, like "How many VIP customers who spent more than $5,000 do I have?" Our AI-powered analyst connects to your data, understands the question, performs the necessary aggregations for you, and instantly builds a live dashboard to provide the answer.