Supermetrics Support Forum

Converting Null Values to Zero

If you need the data for a field to be a zero instead of a null value, so that you use math functions on it properly, you can use calculated columns to convert it.

The basic formula is:

CASE WHEN <yourfieldname> IS NULL THEN 0 ELSE <yourfieldname> END


Here's an example using the 'Comments' count field from Facebook Insights:

Example CASE formula to turn null values into 0 for Data Studio

This technique is necessary when using metrics to create custom calculations for your reports - if any value in the math is null, it will turn the whole result null! To get around this, you can create a new calculated field for each metric with the above formula, and then use these fields instead to build your custom calculations. With the nulls as zeros, it will do the math correctly.

If this does not work:

If you try this and still get "null" value or "0"s for unique count metrics that should have non-zero values (like Reach, views, etc.), you may be running into the "can't be calculated, would require summing deduplicated values" issue. Using the above method won't help with those, you need to use a different approach. Please see this guide for how to address that issue: Can't Be Calculated, Would Require Summing Deduplicated Values


Cosmetic Zero

If you want to convert a value to "0" because it looks nicer in your report but don't plan to use it for calculations, you can also use a setting in Data Studio to change it to render as "0" (or other value to represent no data).


Click on the report object to update and then click the STYLE tab. Scroll down to the "Missing Data" section and adjust the value there.

Orange box highlights "Missing Data" section of the STYLE tab, which has a drop-down to define how no data is displayed


There's many options to choose from:

A list of the options available under "Missing Data", such as "No data", "0", "-", and others

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.