Video |

Tableau | Data prep | Data visualisation

ZN Function in Tableau

Find out why you need to know about this function and how to use it.

Tim Ngwena •4 February 2021 •1 min read •7 min watch •12K

Part ofTableau Functions

Watch on YouTube

ZN() returns the expression if it isn't null, otherwise returns zero, making it ideal for handling nulls in number fields
Nulls left untreated can throw errors or silently skew aggregations like averages, leading to misleading results
Wrapping a field as ZN([Revenue]) and prefixing the calculated field name makes it clear you're handling nulls
Use the calculation editor's function panel to validate what ZN does before applying it
Replacing null with zero improves data literacy since most business users don't understand the term 'null'

0:00Hey it's Tim here. In today's video we're

0:02talking about the ZN function. Now the ZN

0:04function is an

0:05interesting number function because it

0:07allows us to handle nulls whenever we come

0:09across it in a

0:10data set. In today's video I'm going to be

0:12showing you exactly how that works. I've

0:14created a mock

0:15data set that I'll leave a link to in the

0:16description below so you can download that

0:18and use it along with this video. All right

0:20let's get stuck here. Okay I've got a very

0:22simple data

0:23set here. I've got it in excel. It's very

0:25simple. It essentially just tries to

0:26simulate some cinematic

0:28excels in different countries. I've got the

0:30genre on the left, revenue in column B and

0:32in column C

0:33the country. You can see that I have some

0:35empty fields here and this is what the ZN

0:37function is

0:37going to help me deal with. You'll see why

0:39I need to deal with this in Tableau in a

0:41little bit.

0:42But essentially this is our data set. We're

0:43going to get stuck in. I'm going to leave a

0:45description

0:45to this in the description as I've already

0:47said so you can download it and follow

0:49along as you

0:50need to. Now you do need to close the excel

0:51file if you're going to connect to excel.

0:53It can cause

0:54issues when you're trying to connect to an

0:56excel file live whilst working with it

0:58inside of Tableau.

0:59Okay so I've gone ahead and closed that

1:00file. It's going to open up excel file. It

1:02's going to open up

1:03my ZN function data folder and we're just

1:05going to connect to the mock data. Let's go

1:07ahead and

1:08open that and see what happens. And the

1:10next step is essentially you get the data

1:12model window. This

1:13window here and because we don't have any

1:15fancy data prep to work with we can just

1:17hop right into

1:18sheet one by hitting the bottom left option

1:20here. Okay now we've got our data set and

1:22it's perfect.

1:23Let's go ahead and bring some countries in.

1:25So I'm just going to bring in some rows and

1:26you'll see

1:27that we have five countries here that we

1:29can work with and then I'm going to go

1:30ahead and bring in

1:31the revenue for each country and there we

1:33go we have a pretty much perfect data set.

1:36Now you

1:36probably really can't see anything wrong

1:38with this data at this level of aggregation

1:40because

1:40everything is fine. But what I'm going to

1:42do is I'm actually going to create a new

1:44sheet. I'm going

1:44to try and bring in all the information I

1:46need to make this sort of more compelling.

1:49So I'm going

1:49to bring country and I'm going to bring in

1:51genre and then I'm going to bring in the

1:53revenue. Okay

1:55and now you can start to see where the

1:56issues are. You can see that I have this

1:59particular item here

2:00that's empty and in this particular

2:02instance essentially what the zn functions

2:04allows us to do

2:05is to replace that with a zero. Okay so let

2:07me just show you how the function works.

2:09Let me open

2:10up a calculation window and just make this

2:12a little larger so you can all see. Okay so

2:14you

2:14just type in zn it's very very simple you

2:17can see that the automatic list comes up

2:20hit enter

2:21and then asks us for the expressions in

2:23this case it's revenue so I'm just going to

2:25go ahead and

2:25type that in there and you're pretty much

2:27good to go it's as easy as that to type

2:29this up. I normally

2:31add zn to the front of the field so I know

2:33that I'm using the zn function in this

2:35particular field

2:36so I'll just type in here zn revenue and

2:38now that we've done that it's always good

2:41just to validate

2:42your calculation make sure it works make

2:43sure you understand what's going on. In my

2:45previous video

2:46I showed you that you can actually expand

2:48this little area here on the right and if

2:50you click

2:50on the function it gives you a highlight

2:52and it also tells you what the expression

2:55does on the

2:55right hand side so let's just review that.

2:57Returns the expression if it is not null

2:59otherwise returns

3:01zero. Okay so let's hit okay and see what

3:03happens. Let's bring zn here and we're

3:06going to place that

3:07here into this box you can see that it

3:09highlights it for me I'm kind of semi using

3:11the show me

3:12feature here I have no choice in the matter

3:13I know I said don't you show me but I have

3:15no choice in

3:16this matter that's just what I call it when

3:18you do that okay and now you can see that

3:21it actually has

3:22a value 0.0 here which is really really

3:24important let me show you why. Okay let's

3:27say we're doing

3:28some really really pivotal calculations and

3:30we need things like averages and aggreg

3:32ations to work

3:33correctly. In in Tableau quite a lot of the

3:35time if you're doing calculations of

3:37functions and a

3:38null appears in the data set you might

3:40actually get an error and in those

3:42instances you don't want

3:43the error you want to be able to keep on

3:45computing and just assume that the value in

3:48null is actually

3:49zero because that's essentially what it is

3:51in computer terms nothing exists but in

3:53human terms

3:54that's just a value of zero. So basically

3:56what I'm going to do is I'm actually going

3:58to change

3:59one of these fields to be an average so

4:01this sum here you're going to change it to

4:02an average to

4:03see what we get let's go over here make

4:05this an average and you'll see that we get

4:07a list of

4:07values for France it's 7.5831 and so on and

4:11so forth. Now what's going on here is that

4:14if I

4:14actually go into the root data here just

4:16look at this a little second and just go

4:17into the full

4:18data look at what's actually going on you

4:20'll see that the revenue field that it's

4:22actually taken

4:23into consideration is this column here and

4:25you can see there's actually quite a few

4:27nulls in fact if I

4:29look down my whole data set there's

4:31actually enough nulls there to actually

4:34sway my average

4:35because if these values are just zero and

4:37the database of the system instead of

4:39entering the

4:39value of 0.00 just enters a null then our

4:42average is potentially wrong we could walk

4:45away thinking

4:45that our average ticket sales here is seven

4:48pounds fifty when in fact we're giving away

4:50free tickets

4:51and that needs to be taken into

4:52consideration. So let's go ahead and solve

4:55that problem let's close

4:56this and now what we're going to do we're

4:58going to bring this zn revenue item here

5:00and I'm actually

5:01going to drop it in the same square what

5:03that does is it creates a whole load of

5:05complicated

5:06items on the view it changes it to measure

5:08names measure values I'll talk about this

5:10in another

5:11video but essentially what we want to do is

5:14focus here where it says sum zn revenue and

5:17we want to

5:17change this to be an average and when we do

5:20that you'll notice that the average is

5:22different okay

5:24and this is because this average is taking

5:26into account the zero value sales that we

5:29've actually

5:29got which we had quite a few of so this is

5:31actually a really important function

5:33because

5:33sometimes when you're doing calculations if

5:36you don't understand how exactly that data

5:38has been

5:38brought into the data set you're working

5:40with you might not realize that you need to

5:42be using the

5:43zn function to help sort that out it's also

5:45a great way of cleaning up your data

5:47essentially

5:48sometimes you just don't want the the term

5:50null all over your data set you might just

5:52want it to

5:52be a clean set of data and so the zn

5:54function actually can help clear this up

5:57and you know

5:58the term the terminology of null is not it

6:00not common in most businesses null is

6:02actually quite

6:03a data centric term it's something that bi

6:05developers data warehouses know a lot about

6:07but actually your everyday individual when

6:09they see that in a dashboard might not

6:11necessarily

6:11have the data literacy to understand hey

6:14this is a value that just has nothing in it

6:17you kind of

6:17spend a lot of time explaining to someone

6:19that you know nothing and zero are not the

6:21same thing

6:21so to avoid all of that zn is your friend

6:24okay so that's pretty much an example of

6:27how to use it

6:28uh hopefully a good way of using it and

6:29hopefully you now see the importance of why

6:32you should be using it in your data set

6:33that's pretty much it this is a much

6:35shorter video than

6:36the previous one and if you enjoyed the

6:38video you know what to do otherwise i'll

6:39catch you in the

6:40next video

Rollup

The ZN function replaces null values with zero in Tableau, which stops nulls silently skewing calculations like averages and keeps dashboards easier for non-technical users to understand.

Tim uses a mock cinema revenue data set with some empty revenue values by country and genre, connected from Excel. At a high level of aggregation the nulls aren't visible, but they cause problems once you drill into detail or run aggregations like averages.

The Breakdown

What ZN does 0:00

ZN is a number function that returns the expression's value if it isn't null, or zero if it is. It's built specifically for handling nulls wherever they crop up in a numeric field.
Spot the null problem first 1:41

Nulls can hide at aggregate levels of a view and only reveal themselves once you break data down further (e.g. by adding more dimensions). Before reaching for ZN, drill into detail to confirm nulls are actually present and causing an issue.
Write the calculation 2:06

Wrap the field in ZN(), e.g. ZN([Revenue]), and prefix the calculated field's name with ZN so it's clear to you and others that null-handling is happening there.
Validate before you trust it 2:41

In the calculation editor, expand the function panel and click on ZN to see its description confirming it returns the expression if not null, otherwise zero — a quick way to check you understand what a function does before applying it.
Why untreated nulls are risky 3:25

Left alone, nulls can throw errors in calculations or, worse, silently skew results like averages because Tableau may exclude or mishandle them rather than treating them as zero.
See the effect on an average 4:01

Tim changes an aggregation from sum to average with the raw revenue field and gets a value that looks fine but is wrong, because rows with null revenue are treated inconsistently rather than counted as zero sales.
Compare against the ZN'd field 4:55

Swap in the ZN-wrapped field and recompute the same average — the result changes once the zero values are properly counted, showing the true average rather than one distorted by ignored nulls.
Clean data improves literacy 5:41

Beyond fixing maths, ZN removes the term 'null' from dashboards. Most business users don't have the data literacy to understand null versus zero, so presenting a clean zero avoids confusion and explanation overhead.

Worth Knowing

Nulls can look completely fine at a high level of aggregation and only surface once you add more granularity to the view, so don't assume a clean-looking summary means clean data.
Close the source Excel file before connecting to it in Tableau, as a live open file can cause connection issues.
Dropping a field into a box that already holds another measure can trigger Measure Names/Measure Values and complicate the view — expected behaviour, not an error.

Use It When

Reach for ZN whenever a numeric field has nulls that could distort aggregations like averages, or when you want to present a clean zero instead of a null on a dashboard for less technical audiences.

How this Rollup was made provenance & method

A Rollup is drafted by AI from the video's transcript, then reviewed and edited by Tim. Everything used to produce this one is listed below — the model, the exact prompt, and the source video — so the process is transparent and reproducible.

Transcription: On-device — NVIDIA Parakeet v3 for recent videos, OpenAI Whisper large-v3 for earlier ones. The transcript never leaves the machine or gets published.
Drafting: Claude Sonnet 5 in the cloud, from that transcript.
Prompt: The exact Rollup prompt (v2) — the full system prompt, unedited.
Source video: Watch on YouTube
Drafted: 5 July 2026 at 09:38
Reviewed & edited: 5 July 2026 at 09:41 · by Tim Ngwena

Model + prompt + video is everything you'd need to recreate a Rollup like this yourself. The one thing we don't share is the transcript.

Rights. The video and its transcript are the property of TN Media Ltd. Unauthorised use or download is prohibited. © TN Media Ltd.