How to Sum Aggregate Field in Tableau

Running into the dreaded "cannot further aggregate an argument that is already an aggregation" error in Tableau is a rite of passage for many data analysts. You have a calculation that perfectly summarizes one part of your data, but when you try to sum or average that summary, Tableau puts up a wall. This article will show you exactly how to get around this common hurdle using two powerful techniques: Level of Detail (LOD) expressions and Window Functions.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Can't You Sum an Aggregate in Tableau?

First, let's quickly understand why Tableau throws this error. In Tableau, standard aggregations like SUM([Sales]) or COUNTD([CustomerID]) compute a result based on the dimensions present in your view. If you drag Region to Rows and SUM([Sales]) to Text, Tableau calculates the total sales for each region. Each of those SUM(Sales) values is an aggregate - a single summary value calculated from many individual rows of data.

The problem arises when you try to wrap another aggregation around it, like AVG(SUM([Sales])). You're asking Tableau to take the average of summaries, but the standard aggregation functions aren't built to do that. They're designed to work on the row-level data in your data source, not on values that have already been aggregated. In short, Tableau gets confused about what to aggregate.

Thankfully, Tableau provides specific tools designed for this exact challenge. Let's walk through the two best methods to solve this.

Method 1: Use Level of Detail (LOD) Expressions

Level of Detail expressions are the most flexible and robust way to handle nested aggregations. They allow you to compute an aggregated value at a level of detail that is different from what's currently in your view. For our purposes, this lets us calculate an inner aggregate (like total sales per customer), save it as a row-level value, and then perform another aggregation on it.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

A Brief Overview of LOD Expressions

There are three main types of LOD expressions:

• FIXED: Calculates a value for the specific dimension(s) you list, regardless of what other dimensions are in the view. This is the most common one used for handling aggregate-on-aggregate problems.
• INCLUDE: Calculates a value using the dimensions in the view plus any additional dimensions you specify.
• EXCLUDE: Calculates a value using the dimensions in the view, but excluding any dimensions you specify.

We'll focus on FIXED LOD, as it's perfectly suited for "saving" an aggregated value you can use later.

Step-by-Step: Summing an Aggregate with a FIXED LOD

Let's imagine a scenario: You want to calculate the total sales for each product Sub-Category, and then you want to calculate the average of those Sub-Category totals. Trying AVG(SUM([Sales])) by Sub-Category will fail. Here’s how to do it with an LOD expression.

Step 1: Define Your Initial Aggregation

Your first-level analysis is finding the total sales for each product Sub-Category. If you put Sub-Category on Rows and SUM([Sales]) on Text, Tableau handles this easily. Our goal is to work with those individual summary values.

Step 2: Create a Calculated Field with a FIXED LOD

Now, we'll create a calculated field that "stores" the total sales for each Sub-Category. This freezes the aggregation at the Sub-Category level.

1. Navigate to Analysis > Create Calculated Field.
2. Name this field something clear, like "Sales per Sub-Category".
3. Enter the following formula:

{ FIXED [Sub-Category] : SUM([Sales]) }

1. Click OK.

This formula tells Tableau, "For every unique Sub-Category, calculate the total sum of sales. Then, return that single value for every row associated with that Sub-Category." What you've effectively done is turned an aggregate (SUM([Sales])) into a row-level value that you can now use in other aggregations.

Step 3: Use the New LOD Field in Your View

Because your new "Sales per Sub-Category" field is no longer considered an aggregation in the same way, you can now wrap another aggregation around it without causing an error.

Let's say you want to find the average sales total across all Sub-Categories. Simply drag your "Sales per Sub-Category" field into the view and change its aggregation to Average.

• Old way (Causes Error): AVG(SUM([Sales]))
• New Way (Works!): Drag "Sales per Sub-Category" to the view and set the aggregation to Average. In a calculated field, this would look like AVG([Sales per Sub-Category]).

Now if you want to sum this new aggregated field (as the article title suggests), you could simply do SUM([Sales per Sub-Category]). However, this will give you an inflated number because the fixed value is repeated for every row in the partition. The most common use case is actually to find the sum of a distinct set of these pre-aggregated values. For example, by dragging Category to your view, and then AVG([Sales per Sub-Category]), you can see the average sub-category sales for each category.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 2: Use Window Functions (Table Calculations)

Another way to aggregate an aggregate is by using window functions, which are a type of table calculation. Unlike LODs, which operate at the data source level, window functions perform calculations only on the data currently visible in your worksheet. They "look" across a window of data (like a pane, a table, or a cell) to compute a value.

An Overview of Window Functions

The most useful function for this task is WINDOW_SUM(). This function returns the sum of the expression within the current data window. Essentially, it adds up all the values for a given measure that are already displayed in your view.

Step-by-Step: Summing an Aggregate with WINDOW_SUM()

Let's use a different scenario. You have a table showing SUM(Sales) broken down by Region and Category. You want to display a grand total for each Region that sums up the category totals within it.

Step 1: Build the Initial View

1. Drag Region to Rows.
2. Drag Category to Rows, placing it after Region.
3. Drag Sales to the Text card on the Marks pane. The pill should read SUM(Sales).

You now have a text table showing sales for each Category, grouped by Region.

Step 2: Create the Window Sum Calculated Field

Now, let's create our table calculation to sum up the SUM(Sales) values.

1. Go to Analysis > Create Calculated Field.
2. Name it "Regional Total (Window Sum)".
3. Enter the formula:

WINDOW_SUM(SUM([Sales]))

1. Click OK.

This formula tells Tableau to sum up all the SUM(Sales) values within a specific data window, or partition. We'll define that partition in the next step.

Step 3: Add the Calculation to the View and Configure It

1. Drag your new field, "Regional Total (Window Sum)," onto the Text mark card next to SUM(Sales).
2. At first, the numbers may look identical or incorrect. That's because we need to tell Tableau how to compute the window sum.
3. Right-click the "Regional Total (Window Sum)" pill in your view and select Edit Table Calculation.
4. Under Compute Using, select Specific Dimensions.
5. Leave Region unchecked but check the box for Category. This tells Tableau to sum all the category values within each Region pane.

You will now see the Region's total repeated for each Category within that region. This is incredibly useful for calculating things like percent-of-total where you need access to both the line-item value and the partition total in the same view.

**Pro Tip:** The function `TOTAL(SUM([Sales]))` works as a shortcut for `WINDOW_SUM(SUM([Sales]))` and is often easier to configure for straightforward grand totals.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

LODs vs. Window Functions: Which Should You Use?

Both methods solve the problem, but they work very differently and are suited for different situations.

Choose LOD Expressions When:

• You need a reusable, fixed value that you can use across multiple worksheets and in other calculations.
• You need the calculation to be independent of the filters and dimensions in your final visualization (unless using context filters).
• You want to 'flatten' an aggregation so it behaves like a normal row-level field in your data source.
• Example: Calculating customer lifetime value (SUM(Sales)) per Customer ID and then using that value to create customer cohorts (low, medium, high LTV).

Choose Window Functions When:

• The calculation only needs to apply to the specific data currently displayed in a single worksheet.
• You need to perform calculations based on the visual layout of your table or chart (e.g., percent of total, running total, grand total of what is shown).
• The calculation needs to change dynamically as you filter or change the dimensions in the view.
• Example: Showing the grand total of sales for the top 10 products that are currently displayed in a filtered view.

Final Thoughts

Trying to sum an aggregate field in Tableau is one of those frustrating roadblocks that eventually leads you to discover some of the tool's most powerful features. By mastering Level of Detail expressions and Window Functions, you gain precise control over how your data is aggregated, allowing you to move beyond simple summaries and build much richer, more insightful analyses.

Of course, learning calculation syntax for every new question can feel like a bottleneck. At times, the "simple" questions a stakeholder asks, like "how do our product category totals compare year-over-year?", require you to stop your analysis, write complex formulas, and validate the results. At Graphed, we've focused on eliminating that friction. Instead of manually writing calculated fields and table calcs, you can connect your data sources to a live, AI-powered dashboard. Simply ask what you want to see - "show me the average sales per sub-category for each region" - and we generate the interactive visualization for you, handling the nested aggregations and complex logic automatically. You get instant answers, giving you more time to explore insights rather than troubleshooting formulas. You can get started with Graphed for free.