How to Sum Distinct Values in Tableau

Got a dataset where values are repeated and finding the correct total feels like an impossible task? You're not alone. Simply dragging a field onto your Tableau view often leads to inflated numbers because it sums up every single row, including duplicates. This tutorial cuts straight to the solution, showing you how to correctly sum distinct values using Level of Detail (LOD) expressions in Tableau.

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 I Just Use SUM()?

Let's start with a common scenario. Imagine a sales dataset where each row represents a product line item within an order. An order containing three different products will have three rows.

Now, what if your dataset includes a Shipping Cost for each order? In the raw data, that same $10 shipping cost might appear in all three rows for that single order. If you drag Shipping Cost into your view and apply a SUM() aggregation, Tableau will calculate $10 + $10 + $10 = $30. That's triple the actual cost.

The SUM() function adds up a measure for every row in your partition. In contrast, you want to sum a value only once for each unique instance, like per order or per customer. This is precisely where the default summing behavior falls short and a more advanced technique is needed.

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

The Best Way to Sum Distinct Values: Using LOD Expressions

Level of Detail (LOD) expressions give you precise control over the granularity of your calculations, independent of what dimensions are in your final visualization. For summing distinct values, the FIXED LOD expression is your most reliable tool. It lets you anchor a calculation to specific dimensions.

The basic structure of a FIXED LOD looks like this:

{ FIXED [Dimension(s)] : AGGREGATE([Measure]) }

• FIXED: The keyword that tells Tableau to compute the expression only using the dimensions specified.
• [Dimension(s)]: The unique identifier for what you want to calculate over. This could be [Order ID], [Customer name], or [Campaign ID].
• AGGREGATE([Measure]): The calculation to perform on the measure for each unique dimension. Because a single Order ID might still have the shipping cost listed on multiple rows, we use an aggregation like MIN(), MAX(), or AVG() to grab the value just once. Since the value is the same across all rows for that order, any of these will work.

Step-by-Step Example: Correctly Summing Shipping Costs

Let's walk through solving the shipping cost problem we discussed earlier. We want to find the total shipping costs across all orders, where each order's shipping cost is only counted once.

Step 1: The Problem View

First, let’s visualize the problem. If you pull Order ID and Shipping Cost into the Rows shelf and use SUM(Shipping Cost) as your measure, you’ll see the inflated numbers. An order with three items shows a shipping cost of $30 instead of $10.

Example Data Structure:

• Order ID 101, Product A, Shipping Cost $10
• Order ID 101, Product B, Shipping Cost $10
• Order ID 101, Product C, Shipping Cost $10
• Order ID 102, Product D, Shipping Cost $15
• Order ID 102, Product E, Shipping Cost $15

A simple SUM(Shipping Cost) gives you $30 (for 101) + $30 (for 102) = $60, when the correct total is $10 + $15 = $25.

Step 2: Create the LOD Calculated Field

Now, we'll create a calculated field that isolates the shipping cost for each unique Order ID.

1. Right-click on any empty space in the Data pane (the left sidebar) and select Create Calculated Field.
2. Name your calculation something intuitive, like "Unique Shipping Cost per Order".
3. In the formula editor, type the following LOD expression:

{ FIXED [Order ID] : MIN([Shipping Cost]) }

What does this do? It tells Tableau: "For every unique Order ID, find the MIN() value of Shipping Cost and assign it to that Order ID." Since the shipping cost is the same for all rows of a given order, MIN(), MAX(), or AVG() would all return the same correct value ($10 for Order 101).

Click OK to save the calculated field.

Step 3: Use the New Calculation in Your View

You’ve now created a calculation that correctly computes the cost per order. The final step is to sum up those individual order costs.

• Drag your new calculated field, "Unique Shipping Cost per Order," into your view (for example, onto the "Text" card in the Marks pane). Tableau will automatically apply a SUM() aggregation to this new field, becoming SUM([Unique Shipping Cost per Order]).

The result? You will now see the correct total of $25. Your visualization is now summing the distinct values ($10 for Order 101 + $15 for Order 102), solving the duplicate value problem completely.

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

Another Practical Example: Summing Campaign Budgets

Imagine another common scenario in marketing analytics. You have a table with performance data, where each row represents data for a specific day, ad, or keyword from a marketing campaign. The budget for that campaign is listed in a column, so it is repeated on every single row associated with that campaign.

Example Data Structure:

• Campaign A, Day 1, Clicks 50, Budget $1,000
• Campaign A, Day 2, Clicks 80, Budget $1,000
• Campaign A, Day 3, Clicks 65, Budget $1,000
• Campaign B, Day 1, Clicks 120, Budget $2,500
• Campaign B, Day 2, Clicks 150, Budget $2,500

A simple SUM([Budget]) would result in ($1,000 * 3) + ($2,500 * 2) = $8,000. This is clearly not the total budget, which should be $1,000 + $2,500 = $3,500.

To fix this, you would use the exact same LOD approach:

1. Create a calculated field named "Unique Campaign Budget".
2. Enter the formula:

{ FIXED [Campaign Name] : MIN([Budget]) }

1. Bring SUM([Unique Campaign Budget]) into your view to get the correct total of $3,500.

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

An Important Detail: Troubleshooting with Filters

There's one "gotcha" to be aware of when using FIXED LOD expressions: their relationship with filters.

By default, FIXED calculations are computed before regular dimension filters are applied. Imagine you have a view with your correct campaign budget calculation, but you add a filter for Day to only show "Day 1". Your total budget remains $3,500, even though only data from Campaign A ($1,000) and Campaign B ($2,500) for Day 1 is visible.

Why? The FIXED calculation already determined the budgets for Campaigns A and B before your Day filter kicked in.

To make the FIXED LOD respect a filter, you need to elevate that filter to a Context Filter. Simply right-click the filter in the Filters shelf and select Add to Context. The filter will turn a gray/silver color, and now your LOD calculation will compute after the context filter is applied, giving you the expected result based on your filtered data.

Final Thoughts

Dealing with duplicated data is a common hurdle in data analysis, but Tableau's Level of Detail expressions offer a robust solution. By using { FIXED } with an aggregation like MIN() or MAX(), you can easily control your calculations to sum values only once per unique identifier, ensuring accuracy in your reports and dashboards.

We know that mastering calculations in tools like Tableau can feel like learning a new language. It often takes hours of trial and error before you get your reports just right. That’s why we built Graphed. We connect your data sources - like Google Analytics, Shopify, or Salesforce - and let you build dashboards and get insights just by asking questions in plain English. Instead of writing complex LOD expressions, you can just ask, "What was our total campaign budget last month?", and get an instant, accurate visualization.