How to Show 0 if No Data in Tableau

It’s a classic Tableau puzzle: you build a beautiful chart, but gaping blank spaces appear where you expect to see zeros. This happens because Tableau can only plot data that actually exists in your data source, so if there were no sales for a specific product on a certain day, there's no data row, and therefore, no mark. This article will walk you through the most effective methods to fill those gaps and display a zero, turning your sparse charts into complete, easy-to-read visualizations.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Tableau Shows Blanks Instead of Zeros

Before fixing the problem, it’s helpful to understand why it happens. The issue isn't a bug, it’s a reflection of your underlying data structure. Most datasets are "sparse," meaning they only record events that occurred. For example, a sales table will have rows for transactions but likely won't have rows for every possible product-day combination where sales were zero.

Imagine your data looks like this:

Date,Product,Sales
Jan 1,Product A,$100
Jan 1,Product B,$50
Jan 2,Product A,$75
Jan 3,Product B,$120

There are no recorded sales for Product B on January 2nd or Product A on January 3rd. When you drag 'Date', 'Product', and 'Sales' into a Tableau view, Tableau draws marks for the four rows it sees and leaves everything else blank - empty space where 'Jan 2, Product B' would be. To show a zero, we first need to tell Tableau to acknowledge that this data point could exist, a process often called "data densification." Then, we can replace the resulting null value with a zero.

Here are three reliable methods to accomplish this, ranging from a quick fix to a more robust, structural solution.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 1: "Show Missing Values" for Dates and Bins

The easiest solution by far applies when you are working with a continuous date field (the green pill) or a bin. Tableau has a built-in feature to automatically fill in the gaps for these specific data types.

Let's say you have a line chart showing daily sales, but there are gaps on days with no sales. Here’s how to fix it:

Step 1: Enable "Show Missing Values" Right-click on your date pill on the Columns shelf (e.g., DAY(Order Date)). In the context menu, you'll see an option for Show Missing Values. Click it.

Immediately, Tableau will complete the date axis, and you may see your line chart break. This is because Tableau has densified the data - it has created placeholders for the missing dates - but those placeholders contain null values, which break the continuous line.

Step 2: Fill the Nulls with Zeros Using ZN() and LOOKUP() Now we need to replace those nulls with zero. The simplest approach might seem to be wrapping your measure in the ZN() function, which converts nulls to zero. For example, creating a calculated field like ZN(SUM([Sales])). However, this often doesn't work on its own because the SUM([Sales]) for a non-existent row is still null at a foundational level.

To fix this, we need to use a table calculation. The combination of ZN() and LOOKUP() reliably solves this. Create a new calculated field:

ZN(LOOKUP(SUM([Sales]), 0))

Let's break down this formula:

• SUM([Sales]): This first aggregates your measure as usual.
• LOOKUP(..., 0): LOOKUP is a powerful table calculation function that looks at values in other rows of the partition. By using an offset of 0, we are essentially telling it to look at the value in the "current" row. This is key, as it forces Tableau to evaluate the value within the context of the densified table, where it can now "see" the nulls you want to fill.
• ZN(...): This function wraps the entire expression. It takes the result of the LOOKUP - which will be NULL for your missing dates - and converts it to 0.

Drag this new calculated field onto your Rows shelf to replace your original [Sales] pill. Your line chart will now be continuous, correctly dipping down to zero on days where there were no sales.

Method 2: Use a Table Calculation for Discrete Dimensions

What if your dimension isn't a continuous date or a bin? What if you're trying to build a table of Product Category by Region and some combinations are missing? The "Show Missing Values" option won't be available for these discrete dimensions (blue pills).

In this case, the ZN(LOOKUP(SUM([Sales]), 0)) formula from the previous method is still your best friend. Even without explicitly clicking "Show Missing Values," placing discrete dimensions on opposing shelves (one on Rows, one on Columns) can sometimes trigger enough data densification for this table calculation to work.

Here’s the process for a text table view:

1. Place one discrete dimension on the Rows shelf (e.g., 'Product Category').
2. Place another discrete dimension on the Columns shelf (e.g., 'Region').
3. Drag your new ZN(LOOKUP(SUM([Sales]), 0)) calculated field onto the Text mark on the Marks Card.

Tableau will compute the table calculation and fill in the empty cells with zeros. If it still doesn't work, ensure you've checked the "Show Empty Rows" or "Show Empty Columns" settings under Analysis > Table Layout.

The key takeaway is that this table calculation works within the "visual table" that Tableau has rendered. It evaluates after the initial data query and can fill gaps that exist within the visualization's structure.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 3: Data Scaffolding for a Bulletproof Solution

Sometimes, the built-in Tableau features and table calculations aren't enough, especially when dealing with multiple discrete dimensions or when you need a solution that is guaranteed to work every time, regardless of visual layout. This is where data scaffolding comes in.

Scaffolding involves creating a separate, complete framework of all possible dimension combinations and joining it to your sparse transactional data. This physically creates the "missing" rows at the data source level so Tableau has no choice but to recognize them.

Step 1: Create a Scaffold File Your scaffold is a simple file (often an Excel or Google Sheet) that contains a complete list of unique values for each dimension you need in your view. For example, if you want to analyze Market by Product Line, your scaffold file would need two columns: one with every single Market and one with every single Product Line.

Step 2: Connect to Data sources in Tableau In Tableau, first, connect to your scaffold file. Then, add a connection to your main data source (e.g., your sales data).

Step 3: Join or Relate the Data On the Data Source tab, drag your scaffold table to the canvas first, then drag your actual sales data next to it. Tableau will prompt you to create a relationship or join. You want a Left Join or a relationship where the scaffold table is on the left.

Set the join or relationship clause to connect on the fields the two sources share. For example, connect Market (Scaffold) to Market (Sales Data) and Product Line (Scaffold) to Product Line (Sales Data).

A left join in this configuration ensures that you keep every single row from your scaffold file, regardless of whether a matching sale exists in your transactional data. When there's no match, the sales measure will simply be NULL.

Step 4: Build Your View and Use a Simple Calculation Now, go to a worksheet and build your view by dragging the dimension fields from your scaffold data source. When you bring in your measure (e.g., [Sales]) from your main data source, you will see a bunch of blanks again.

But this time, because the rows actually exist, a simple calculated field will work perfectly. Create a new calculation:

ZN(SUM([Sales])) ... or alternatively ... IFNULL(SUM([Sales]), 0)

Use this new calculated field in your view, and all the nulls will be replaced by zeros. The scaffolding technique is the most robust and flexible method because it solves the problem at its root - the data level.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Which Method Should You Choose?

• Use Method 1 ("Show Missing Values") whenever you're using continuous dates or bins. It's the fastest and requires no special calculations.
• Use Method 2 (Table Calculation) for quick fixes with discrete dimensions when you can't or don't want to alter your data source. It's excellent for ad-hoc analysis.
• Use Method 3 (Data Scaffolding) when you need a permanent, foolproof solution that works for any combination of dimensions. It requires more up-front work but is the most reliable for formal dashboards and reporting.

Final Thoughts

Dealing with nulls and missing values is a common challenge in any data visualization tool. By understanding why Tableau shows empty spaces, you can choose the right technique - be it a simple menu click, a clever table calculation, or a solid data scaffold - to transform those blanks into meaningful zeros, making your reports more complete and easier for your audience to understand.

While mastering these Tableau techniques is a valuable skill for any analyst, sometimes the goal is getting a quick, complete answer without fiddling with calculations or data joins. This is why we designed Graphed to simplify the reporting process. You can connect all your data sources and use natural language to create the charts you need - like "Show me last quarter's sales by market and product," - and get a real-time dashboard instantly. We handle the data connections and complexities in the background, so you always see a complete picture, with no gaps or missing data to fix, turning tedious report building into a matter of seconds.