What Does the ZN Calculation Do in Tableau?

If you've spent any time building reports in Tableau, you've probably run into empty cells or strange gaps in your charts. These are often caused by NULL values - the database equivalent of a blank or unknown answer. While they might seem harmless, they can break your calculations and make your dashboards confusing. This is where Tableau's ZN() function becomes an essential tool in your analytics toolkit. We'll walk through what the ZN() function does, why it's so important, and how you can use it to create cleaner, more accurate reports.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Annoying Problem with NULL Values

Before we get into the solution, let’s quickly cover why NULLs are such a headache. A NULL value isn’t the same as zero. A zero means we know the value and it is exactly zero. A NULL means the value is missing, unknown, or doesn't exist. It's an empty placeholder.

Imagine you have a table of monthly sales data for different products. If a new product had no sales in its first month, the database might store that value as NULL instead of 0. Why does this matter? Because in data analysis, NULLs behave unpredictably:

• They disrupt calculations: Most mathematical operations involving a NULL value will result in a NULL. For example, 100 + NULL = NULL. This can throw off your total revenue calculations or any other metric you’re trying to sum or average.
• They create visual gaps: In a line chart or a crosstab table, NULLs show up as blank spaces. This can make your reports look incomplete and be confusing for your audience, who might wonder if data is missing or if your report is broken.
• They affect aggregations: Functions like COUNT and AVG treat NULLs differently. COUNT([Sales]) will ignore rows where Sales is NULL, which could misrepresent your data if not handled carefully.

Cleaning up these NULLs is a foundational step in building reliable and easy-to-understand dashboards. Fortunately, Tableau gives us a simple and powerful function to do just that.

What is the ZN() Function in Tableau?

The ZN() function is a simple calculation that checks if a value is NULL. The name itself is a mnemonic: it stands for "Zero Null." Its job is straightforward:

• If the value it checks is not NULL, it returns the original value.
• If the value it checks is NULL, it returns a 0.

That's it. It’s a clean and direct way to swap any empty numerical value with a zero, ensuring your math works as expected and your tables look complete. This is especially useful for any measure (a field you can do math on, like Sales, Sessions, or a Lead Score).

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Syntax of ZN()

The formula for the ZN() function is as simple as it gets:

ZN([Your Measure])

You just place the name of the numeric field you want to check inside the parentheses. For instance, if you want to replace nulls in your "Sales" field, your calculation would be ZN([Sales]).

How to Use ZN() in Tableau: A Step-by-Step Guide

Let's walk through creating a calculated field using ZN() to clean up some sales data. Imagine we have a table showing sales by region, but some regions have no sales recorded, leaving ugly blank spots in our view.

Our goal is to fill those blanks with zeros.

Step 1: Create a New Calculated Field

First, we need to create a new field that will house our ZN() logic. In your Tableau worksheet, right-click on your measure in the Data pane (in our case, "Sales"). From the dropdown menu, select Create > Calculated Field...

Step 2: Name Your Field and Write the Formula

A calculation editor window will pop open. Let's give our new field a clear name, something like "Sales (ZN)" or "Sales - Zero When Null." This helps you remember what the field does later.

In the formula box, type:

ZN([Sales])

A green checkmark will appear at the bottom of the editor, indicating "The calculation is valid." Click OK.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Replace the Old Measure with the New One

Now, you'll see your new calculated field, "Sales (ZN)," in the Measures section of the Data pane. To fix our table, simply drag the original [Sales] measure off the view and drag your new [Sales (ZN)] measure on to replace it.

Instantly, all the blank spots in your table will be filled with zeros. The view is now cleaner, easier to read, and less likely to be misinterpreted by your team or stakeholders.

Practical Use Cases for the ZN() Function

Replacing blanks is the most common use, but ZN() is a versatile function that can solve several other data analysis challenges.

1. Fixing Broken Mathematical Formulas

Let's say you want to calculate profit with a simple formula: [Sales] - [Costs]. If a product has sales data but is missing cost data for a given month, then its [Costs] field might be NULL. The whole profit calculation would then become:

$5000 (Sales) - NULL (Costs) = NULL (Profit)

Your entire profit calculation would be broken for that row, making your overall profit numbers inaccurate. We can fix this by wrapping both measures in the ZN() function:

ZN([Sales]) - ZN([Costs])

Now, even if [Costs] is NULL, the formula treats it as zero, and your calculation works perfectly: $5000 - 0 = $5000.

2. Understanding Averages Correctly

This is a subtle but critical point where you need to be careful. Let’s say you are calculating the average daily sales for a store over a 30-day period. The store was closed for two days, and on those days, the sales data is NULL.

• If you use AVG([Sales]), Tableau will calculate the average based only on the days with sales data (28 days). It completely ignores the two days where sales were NULL. This answers the question: "What were my average sales on days we made a sale?"
• If you use AVG(ZN([Sales])), the formula first converts the two NULL values to zero. When you average the sales, those two zero-sale days are included in the calculation (dividing the total sales by 30 days). This answers the question: "What were my average daily sales over the entire 30-day period?"

Neither is right or wrong - they answer different business questions. Using ZN() gives you control over whether to include those "zero" days in your denominator, ensuring your average accurately reflects the question you're trying to answer.

Graphed

Still Building Reports Manually?

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

Watch the Demo

ZN() vs. IFNULL(): What's the Difference?

As you get more comfortable with Tableau, you'll discover another function called IFNULL(). It’s tempting to think they do the same thing, but there's a key distinction.

The ZN() function is just for numbers and always replaces NULL with zero. It’s a specific, one-job tool.

The IFNULL() function is more flexible. It checks if the first value is NULL and, if it is, allows you to replace it with a second value that you specify. The syntax is:

IFNULL([Field to Check], [Value to use if NULL])

This is incredibly useful when you're not working with numbers or when you want to replace a null with something other than zero. For example:

• Working with text fields: You could use IFNULL([Customer Segment], "Unknown") to replace empty segment data with the word "Unknown."
• Using a default numeric value: Perhaps your default order quantity should be 1, not 0. You could use IFNULL([Order Quantity], 1).

So, a good rule of thumb is:

• For any numeric field where you want NULLs to become zero, use ZN(). It's faster and clearer.
• For text fields, or when you need to replace NULLs with a specific value other than zero, use IFNULL().

Final Thoughts

The ZN() function is a simple but mighty tool for handling one of the most common data cleanup tasks in Tableau. By proactively converting NULL values into zeros, you can build dashboards that are more resilient to strange data, produce accurate calculations, and are far easier for your audience to understand and trust.

While mastering functions like ZN() is a great skill for any analyst, we understand that not everyone wants to get bogged down in learning formulas and wrangling data in traditional BI tools. We created Graphed because we believe getting insights shouldn't require you to become a data specialist. Instead of writing formulas, you can just connect your data sources - like Google Analytics, Shopify, or HubSpot - and ask questions in plain English, like "Show me my sales by region for last month as a table." Graphed instantly builds the report, handling all the data complexities for you so you can get the answers you need in seconds, not hours.