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)
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:
and none of them have worked for either allowing division in all cases or allowing my measure into the aggregate awareness table.