How to Avoid Dividing by Zero

Looker Q&A Looker
1

When creating percentage measures, it is often useful to make sure you are not dividing by zero in the percentage calculation. This can be done through the SQL function NULLIF(expression_1, expression_2).

NULLIF takes two inputs. If the two expressions are equal, NULLIF returns a NULL. If the two expressions are not equal, NULLIF returns the value of the first expression.

So, in order to avoid dividing by zero in a dimension, we might use NULLIF(${field_name}, 0). This means “if ${field_name} is 0, give me a NULL, otherwise give me ${field_name}”.

Here is an example of a percentage measure using NULLIF:

- measure: percent_sold 
  type: number                                 
  format: "%.2f%"                               
  sql: 100.0 * ${sold_count} / NULLIF(${count}, 0)
6 Likes
2

This solution is great and works well for almost all circumstances.

That being said, if you’re trying to include a value like this into an Aggregate Awareness table, it will reject the measure because it doesn’t support those measure types. I would be curious to hear if anyone has found a workaround, because I’ve tried tried the following strategies:

  1. Incorporate NULLIF into the original number dimension
  2. Incorporate NULLIF into the sum measure
  3. Add a filter to the sum measure where the value cannot be 0

and none of them have worked for either allowing division in all cases or allowing my measure into the aggregate awareness table.