Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Exclude Null Values in Tableau Calculation

Cody Schneider••8 min read

Working with null values in Tableau can quickly turn a clear visualization into a confusing one. These empty fields can skew your calculations, mess up your averages, and leave gaps in your charts. This tutorial will walk you through several practical methods for excluding or handling null values directly within your calculations, ensuring your data analysis is accurate and your reports are clean.

GraphedGraphed

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.

Watch Graphed demo video

What Are Null Values and Why Are They a Problem?

In data analysis, a null value represents missing or unknown information. It’s not the same as zero or an empty string, it’s a placeholder indicating the absence of a value. For example, if a customer hasn't made a second purchase yet, their "Repeat Purchase Date" field might be null.

Nulls create challenges in Tableau for a few key reasons:

  • Distorted Calculations: Mathematical operations involving a null value often result in a null. For instance, 100 + NULL returns NULL. This can wreck your aggregate calculations like sums and averages if not handled properly.
  • Inaccurate Averages: This is a big one. Let's say you have sales figures: $100, $200, NULL. If you want the average of recorded sales, the answer should be $150 ((100 + 200) / 2). But if you incorrectly handle the null and treat it as zero, the average becomes $100 ((100 + 200 + 0) / 3), which is a completely different insight.
  • Visualization Gaps: In line charts or other visualizations, null values can create breaks in the data, making trends harder to interpret. They can also lead to entire marks or rows disappearing from your view unexpectedly.

Knowing how to manage these nulls is fundamental to building trustworthy dashboards. Let's look at the best ways to do it.

Method 1: Create a Filter to Exclude Nulls

The most straightforward way to remove nulls is to filter them out of your worksheet entirely. This method removes the entire row containing the null value for a specific field from your visualization. This is the right choice when the presence of a null in a key field makes the entire record irrelevant to your analysis.

For example, if you're analyzing sales performance, you likely want to exclude any leads that don't have an associated closed-won value.

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.

Step-by-Step Instructions

  1. Find the field (dimension or measure) that contains null values in the Data pane on the left.
  2. Click and drag that field onto the Filters shelf.
  3. A filter dialog box will appear. If it’s a dimension, you’ll see a list of values. Uncheck the ‘Null’ checkbox at the bottom of the list.
  4. If it’s a measure, a dialog box titled "Filter Field" will appear. You can select "All values" and then click the "Special" tab in the next prompt. From there, select "Non-null values".
  5. Click OK.

Now, your visualization will update, and all rows where that specific field was null will be excluded from the view and any worksheet calculations.

Pros and Cons of Filtering

  • Pro: It's quick, intuitive, and doesn't require writing any code or calculations.
  • Con: It’s an all-or-nothing approach. By removing the entire row, you might accidentally exclude important contextual data from other columns in that row you wanted to keep.

Method 2: Use Calculated Fields to Handle Nulls

Sometimes filtering out an entire row is too drastic. You might want to keep the row but simply substitute the null with a different value for a specific calculation. This is where calculated fields are incredibly powerful. They give you precise control over how nulls are treated.

Using the ZN() Function for Zeros

The ZN() function is a simple and effective shortcut that stands for "Zero Null." It checks a measure, and if the value is null, it returns a 0, otherwise, it returns the field's actual value.

The syntax is straightforward:

ZN([YourMeasure])

Example: Calculating Total Sales

Imagine you have a [Sales] field with some nulls. If you just tried to create a SUM([Sales]) calculation in some contexts, the nulls could be ignored, but what if you're doing a row-level calculation first, like [Sales] + [Bonus]? If either value is null, the result will be null.

To fix this, you can create a new calculated field called "Sales (ZN)":

ZN([Sales])

Now, when you use SUM([Sales (ZN)]), every null is treated as a 0, preventing errors in more complex row-level calculations before an aggregation occurs. This is perfect for financial data where a missing entry often means zero activity.

Using the IFNULL() Function for Flexible Substitution

The IFNULL() function is a slightly more flexible version of ZN(). It allows you to replace a null value with whatever you specify, not just zero.

The syntax is:

IFNULL([FieldToTest], [ReplacementValue])

GraphedGraphed

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.

Watch Graphed demo video

Example: Substituting a Previous Quarter's Sales

Let's say you want to assume that if a product's sales for the current quarter are null, they should be represented by last quarter's sales figure for forecasting purposes. ZN() wouldn't work because you don’t want to use zero.

You could create a calculated field like this:

IFNULL([Current Quarter Sales], [Last Quarter Sales])

This checks the [Current Quarter Sales] field. If it contains a value, it uses it. If it's null, it pulls the value from the [Last Quarter Sales] field instead.

Using the ISNULL() Function for Logical Tests

The ISNULL() function is a logical function that returns either TRUE or FALSE. It's used inside other functions, typically in an IF statement, to build more complex logic. ISNULL([Field]) will return TRUE if the field's value is null.

This is extremely useful when you need to do something entirely different for null vs. non-null values.

Example: Calculating a Discount Rate

Imagine your dataset has a [Discount] field that is sometimes null. To calculate the final price, you might use [Price] * (1 - [Discount]). But if [Discount] is null, the entire result will be null. You can use ISNULL() to handle this:

IF ISNULL([Discount]) THEN
    [Price]
ELSE
    [Price] * (1 - [Discount])
END

This calculation checks if [Discount] is null. If it is TRUE (meaning the field is null), it returns the original [Price]. Otherwise, it applies the discount calculation. This approach ensures you never get a null final price just because a discount wasn't entered.

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.

Choosing the Right Method for Your Scenario

So, which should you choose? It all depends on what your data means and what question you're trying to answer.

  • Use a filter when: The entire record is invalid or irrelevant to your analysis if a key value is missing. For example, "Show me survey results only from respondents who answered the 'Overall Satisfaction' question."
  • Use ZN() when: You are working with numerical measures, and a null should logically be treated as zero in your aggregates. For example, "Calculate total units sold, where blank entries mean zero sales."
  • Use IFNULL() when: You need to substitute the null with a specific value that isn't zero, or with a value from another field. For example, "If the 'Contact Name' is null, use the 'Company Name'."
  • Use ISNULL() when: You need to create custom logical pathways and perform different actions for null and non-null values. For example, "If a ship date is null, label the order status as 'Pending', otherwise, label it 'Shipped'."

A Critical Warning About Averages

Be extremely careful when handling nulls in average calculations. The method you choose fundamentally changes the result.

Consider the data: [10, 20, 30, NULL]. You want to find the average.

  1. Filtering the Null: Tableau's default AVG() aggregation ignores nulls automatically. The calculation is (10 + 20 + 30) / 3 = 20. This calculation answers the question: "What is the average value of the entries we have?"
  2. Using ZN(): If you use AVG(ZN([Your Field])), the calculation becomes (10 + 20 + 30 + 0) / 4 = 15. This answers: "What is the average value across all records, assuming missing values are zero?"

Neither answer is "wrong," but they represent completely different business realities. Always stop and think about which question you are trying to answer before choosing a method.

Final Thoughts

Mastering null values is a key step toward reliable and accurate reporting in Tableau. By using direct filters or calculated fields with functions like ZN(), IFNULL(), and ISNULL(), you can transform messy data into a clean, insightful analysis. Choosing the right technique depends on whether you need to exclude records entirely or just control how missing values are treated within a calculation.

For many teams, the process of data cleaning, writing formulas, and checking for things like nulls is frankly a repetitive time-suck. We built Graphed because we believe there's a faster way. Instead of manually applying filters or writing IFNULL calculations every time, you can now just ask in plain English: "Show me average sales by quarter but treat any missing sales data as zero," and our AI data analyst builds the chart for you correctly. It's about getting straight to the insight without getting stuck wrestling with the data first.

Related Articles