Supermetrics Support Forum

Converting Null Values to Zero

If you need the data for a field to show as a zero instead of a null value, 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 also 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

Did you find it helpful? Yes No

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