How to Avoid Dividing by Zero

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 1 13.7K
1 REPLY 1

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.

Top Labels in this Space
Top Solution Authors