Why is my percent or average measure showing zero (or rounding to an integer) aka where are my decimals?

[LookerExperts]

Knowledge Drop

Last tested: May 1, 2020

Here's a Help Center article to explain this question.

Explanation

If you divide two integers in SQL, you get an integer. Thus 1/2 = 0.

Workaround

The workaround is to cast one integer to a float. An easy way to do that is to multiply by 1.0.

It is very important that the multiplying by 1.0 happens before the division operation.

Working examples:
1.0 * ${count} / ${sum}
${count} * 1.0 / ${sum}
${count} / ( ${sum} *1.0)

What won't work:
${count} / ${sum} *1.0
1.0 * ($[count}/ ${sum})

Note for Druid Database:
${count} * 1.0 / ${sum} will not work. Put 1.0 to the denominator instead ---> ${count} / (${sum} *1.0)
 

Redshift Edge Case

Dividing by two SUMs in Redshift seems to default to 4 places of precision, as noted in this Stackoverflow article. The workaround is to cast one of them to a higher numeric datatype. ${sum1}::numeric(19,6)/${sum2}

This also happens to measures of type:average on Redshift when the field being averaged is an integer.

Presto Edge Case

Using 1.0 * ${variable} still will treat the quotient as a int (mentioned in this Stackoverflow) so casting as a double would resolve this. Something like CAST(${variable} AS DOUBLE) will hopefully do the trick in treating it as a non-int

This content is subject to limited support.