Why are my division fields (averages, percentages, etc.) showing 0 or missing decimals?

When you divide two whole numbers in Postgres or Redshift, you get a whole number back - no decimals. For example, dividing whole numbers gives you integer results. So 3 / 4 returns 0, not 0.75.

This means metrics like completion rates or percentages might show up as 0 or missing their decimal places entirely.

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

Your results might look like this:

The completion rate shows 0.00 instead of 0.33 - that's the problem.

To fix this, 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

Now your results will show the correct decimal values: