All CollectionsFAQs
Why are fields using division (average, percent, etc.) ignoring decimals? Or, showing up as 0?
Why are fields using division (average, percent, etc.) ignoring decimals? Or, showing up as 0?

Average calculation doesn't show decimals, rounded incorrectly, division showing up as 0.

Written by Katie Hindson
Updated over a week ago

When you divide two whole numbers (e.g. two counts) in Postgres or Redshift, the value returned will be an integer value, ignoring any decimals. For example, `3 / 4` will return `0`.

This can affect metrics and table calculations you define in Lightdash.

For example, if you're using Redshift or Postgres and you define metrics like this:

`models:  - name: orders    meta:      metrics:        order_completion_rate:          type: number          sql: \${total_completed_orders} / \${total_orders}          round: 2 # this should return 2 decimal places        percent_orders_with_voucher:          type: number          sql: 100 * \${total_orders_with_voucher} / \${total_orders}          round: 2`

Then your results could have Order completion rate with a value of 0 and Percent orders with voucher with empty decimal values:

 Date Month Total completed orders Total orders with voucher Total orders Order completion rate Percent orders with voucher 2024-03 3 6 9 0.00 66.00

These results are incorrect.

## To fix this, you need to multiply your numerator by 1.00 in the metric definition.

If you multiply your numerator by a value with decimals (e.g. 1.00), then the values will be cast as floats and your results will return decimals, like you'd expect.

So, you'd change your metric definitions to something like this:

`models:  - name: orders    meta:      metrics:        order_completion_rate:          type: number          sql: 1.00 * \${total_completed_orders} / \${total_orders}          round: 2         percent_orders_with_voucher:          type: number          sql: 100.00 * \${total_orders_with_voucher} / \${total_orders}          round: 2`

Then your results will correctly show decimal values, like this:

 Date Month Total completed orders Total orders with voucher Total orders Order completion rate Percent orders with voucher 2024-03 3 6 9 0.33 66.66