How to Exclude Values in Tableau Calculated Field

Building dashboards in Tableau often involves dealing with messy, real-world data, which sometimes includes values you need to ignore. Creating a calculated field that specifically excludes certain records is the cleanest way to do this without altering your original data source. This article will walk you through several methods for excluding values in a Tableau calculated field, from simple IF statements to more advanced Level of Detail expressions.

Why Exclude Values in a Calculated Field?

Before jumping into the "how," let's touch on the "why." You're not just deleting data, you're creating a new, more refined measure for your analysis. Here are a few common reasons you might need to exclude values:

• Cleaning Data: Your data might contain test transactions, internal sales, or dummy accounts that shouldn't be part of your official reporting. Excluding them ensures your metrics are accurate.
• Focusing Analysis: You may want to analyze performance without including a specific outlier region, a newly launched (and low-volume) product, or a campaign that was cancelled mid-flight.
• Creating "What-If" Scenarios: By excluding a major product line or sales channel, you can see how the rest of your business is performing in isolation. For instance, "What does our growth look like without our flagship product?"

Using a calculated field for this task is a best practice because it's non-destructive. Your original data remains untouched, giving you the flexibility to use both the complete and the filtered data sets in your visualizations. Now, let’s get building.

Method 1: The Simple IF Statement

The most straightforward method to exclude a single value or category is leveraging an IF statement. This approach checks for a condition and then returns a value based on whether that condition is true or false.

The logic is simple: "IF the [Dimension] is NOT the value I want to exclude, THEN give me the [Measure]."

Example: Excluding "Internal" Sales from a Total Revenue Calculation

Imagine you have a sales dataset, and a dimension called [Sales Channel] contains values like "Online," "Retail," and "Internal." The "Internal" sales represent employee purchases and shouldn't be included in your external revenue reporting.

Step-by-Step Instructions:

1. Right-click in the Data pane on the left side of your Tableau worksheet and select Create Calculated Field.
2. Name your new calculated field something descriptive, like [External Sales].
3. In the formula editor, type the following expression:

// This formula checks if the sales channel is not 'Internal' IF [Sales Channel] <> 'Internal' THEN [Sales] END

4. You'll see a message at the bottom of the editor that says "the calculation is valid." Click **OK**.

That's it! You now have a new measure in your Data pane called `[External Sales]`. When you drag this onto your worksheet, Tableau will automatically sum it up, but only for records where the `[Sales Channel]` is not "Internal." Any row with "Internal" as the channel will be treated as null for this calculation and therefore won't be included in the sum, average, or count.

## Method 2: The CASE Statement for Multiple Exclusions

What if you have several values to exclude? Chaining multiple `IF...ELSEIF...` statements can work, but a `CASE` statement is cleaner and often more efficient for this scenario. A `CASE` statement checks a single dimension against a list of possible values.

However, the basic `CASE` statement is designed for direct matches. To use it for exclusion, we can combine it with another calculated field or a clever little logical trick with a `BOOLEAN` calculation.

### Example: Excluding Multiple Test User Groups

Let's say a dataset has a `[User Group]` dimension with a few groups you need to exclude: "Testers," "Internal QA," and "Beta Users."

#### Step 1: Create a Boolean Calculated Field to Identify Exclusions

First, we create a field that flags the records we want to get rid of. It will return `TRUE` if a record should be excluded and `FALSE` if it should be kept.

1. Create a new calculated field and name it `[Is Test User]`.
2. Enter the following formula:

// Returns TRUE for any user group that needs to be excluded CASE [User Group] WHEN 'Testers' THEN TRUE WHEN 'Internal QA' THEN TRUE WHEN 'Beta Users' THEN TRUE ELSE FALSE END

3. Click **OK**.

#### Step 2: Use the Boolean Field in a Second Calculation

Now, create a second calculated field that uses this `[Is Test User]` flag to calculate your desired measure.

1. Create another calculated field and name it `[Core User Engagement]`.
2. Enter this simple `IF` statement:

// If the user flag is NOT true, return the Session Mins IF NOT [Is Test User] THEN [Session Mins] END

3. Click **OK**.

This two-step process makes your logic much more readable and modular. If you ever need to add another group to the exclusion list, you only have to edit the `[Is Test User]` calculation, and all the formulas that depend on it will update accordingly.

## Method 3: Using a Fixed LOD Expression for Complex Scenarios

Sometimes your exclusion logic is more complex. What if you want to perform a calculation that completely ignores a certain category at a higher level than your visualization? This is where Level of Detail (LOD) expressions shine, specifically the `FIXED` expression.

A `FIXED` LOD expression computes a value for the specified dimensions, regardless of what other dimensions are in your view. We can use this to create powerful, context-aware filters.

### Example: Calculating Market Share Without an Outlier Product

Imagine you have a new experimental product, `Product-X`, that had unusually high, subsidized sales, and you want to calculate the per-product share of revenue *as if Product-X never existed*. Simply dragging `Product Name` to the filter shelf would remove it from the view, but the `% of Total` calculation would still be based on a grand total that includes `Product-X`.

Here’s how to calculate a new total that excludes a specific product and then use that to compute a more representative market share.

#### Step-by-Step Instructions:

1. **Create a Calculated Field for Total Sales Excluding Product-X.**

We'll use a `FIXED` expression to calculate the total sales across the entire dataset, containing a condition to exclude our target product.

a. Open the calculated field editor and name it `[Total Sales (Excl. X)]`.

b. Enter the following formula:

// FIXED({}) computes one value for the entire dataset { FIXED : SUM(IF [Product Name] <> 'Product-X' THEN [Sales] END) }

c. Click **OK**.

2. **Create the Final Market Share Calculation.**

a. Name it `[Percent Share of Total (Excl. X)]`.

b. Enter the formula:

SUM([Sales]) / MAX([Total Sales (Excl. X)])

We use `SUM([Sales])` to get the sales for the product(s) in the row. We use `MAX()` on our LOD field because LODs can return multiple identical values (one for each row), so we need to aggregate it, even though they will all be the same. `MIN()` or `AVG()` would also work.

3. **Set the Number Format:**

Right-click this new field in the Data pane, go to **Default Properties** > **Number Format...** and set it to **Percentage**.

Now, when you build a bar chart with `[Product Name]` on Rows and `[Percent Share of Total (Excl. X)]` on Columns, you can filter out `Product-X` from the view itself, and your percentages will correctly add up to 100% based on the new, modified total. This gives you a true sense of market share within the relevant product set.

## Best Practices for Clean Calculations

- **Comment Your Code:** Use `//` to add comments explaining your logic. It helps your future self and your colleagues understand what you were trying to accomplish.
- **Use Descriptive Naming Conventions:** A name like `[Sales (Excludes Internal)]` is so much clearer than `[Sales Calc 2]`.
- **Test Your Calculation:** Build a simple crosstab view before using a complex calculation in a final dashboard. Put relevant dimensions and your original and calculated measures side-by-side to ensure the logic is working as you expect.

## Final Thoughts

Mastering how to exclude values in your Tableau calculations is a fundamental skill that moves you out of drag-and-drop analysis and into creating genuinely insightful reports. Whether you use a simple `IF` statement, a more structured `CASE`, or a powerful `LOD` expression, these techniques give you the precision needed to tell the real story behind your data.

Learning the syntax for different tools and mastering concepts like LODs is part of the work, but often the preceding step - just getting all your scattered marketing and sales data into one place - is the biggest challenge. This is especially true for marketing agencies and e-commerce companies dealing with dozens of data sources. To help with this, we built [Graphed](https://www.graphed.com/register) to connect to all your platforms like Google Analytics, Shopify, and Facebook Ads in a few clicks. You can then use plain English to build real-time, interactive dashboards, allowing you to ask your fundamental business questions without first getting bogged down in formulas.