A measure can be built from other measures, such as Revenue per Customer or Units per Order. Its formula works out each input measure in the reader’s context, then combines the results. So a ratio is always the ratio of two totals, at whatever grouping the reader asks for.

How it works

Inside a MEASURE, EVAL(m) works out the measure m in the context of the reader: its grouping and its filters. The formula then combines the values. Kater adds up first and divides last.

A reader that groups by region gets the same three steps once per region. No reader ever gets an average of other rows’ ratios.

Syntax

1
MEASURE('name', EVAL(measure_a) / NULLIF(EVAL(measure_b), 0)) AS "Label"

Examples

Revenue per Customer

The Region Mix query declares Revenue per Customer as Revenue divided by Customers. This query declares the same measure for USD orders, grouped by region:

1
SELECT
2
  shop.region,
3
  shop.currency,
4
  shop.sum__amount,
5
  shop.count_distinct__customer_id,
6
  MEASURE(
7
    'revenue_per_customer',
8
    EVAL(shop.sum__amount) / NULLIF(EVAL(shop.count_distinct__customer_id), 0)
9
  ) AS "Revenue per Customer" /* @format currency(currency, 2) */
10
FROM workspace.shop AS shop
11
WHERE
12
  shop.currency = 'USD'
13
GROUP BY
14
  shop.region,
15
  shop.currency
16
ORDER BY
17
  shop.region

The same query grouped by currency only gives the value for all USD orders:

1
SELECT
2
  shop.currency,
3
  shop.sum__amount,
4
  shop.count_distinct__customer_id,
5
  MEASURE(
6
    'revenue_per_customer',
7
    EVAL(shop.sum__amount) / NULLIF(EVAL(shop.count_distinct__customer_id), 0)
8
  ) AS "Revenue per Customer" /* @format currency(currency, 2) */
9
FROM workspace.shop AS shop
10
WHERE
11
  shop.currency = 'USD'
12
GROUP BY
13
  shop.currency

The average of the five region rows is $367.30, not $1,544.44. Most customers ordered in more than one region, so the region rows count them 890 times in all. The measure for all USD orders divides all the revenue by the 240 different customers.

Save the first query as usd_revenue_per_customer. A query that reads it and groups by currency only gets $1,544.44 too. It calculates both totals again, then divides:

1
SELECT
2
  q.currency,
3
  q.revenue_per_customer
4
FROM query.usd_revenue_per_customer AS q
5
GROUP BY
6
  q.currency

Units per Order

Units and Orders can be added up across every dimension, so a ratio of the two needs no currency rule:

1
SELECT
2
  shop.channel,
3
  shop.sum__quantity,
4
  shop.count_distinct__order_id,
5
  MEASURE(
6
    'units_per_order',
7
    EVAL(shop.sum__quantity) / NULLIF(EVAL(shop.count_distinct__order_id), 0)
8
  ) AS "Units per Order" /* @format number(2) */
9
FROM workspace.shop AS shop
10
GROUP BY
11
  shop.channel
12
ORDER BY
13
  shop.channel

With no grouping, the same measure gives 9,154 units divided by 3,000 orders: 3.05.

A formula over two measures declared beside it

A measure can read measures that the same SELECT declares. Names bind before any measure is worked out, so the order of the declarations does not matter. Here Units per Large Order sits first and reads the two measures below it:

1
SELECT
2
  shop.region,
3
  MEASURE(
4
    'units_per_large_order',
5
    EVAL(large_units) / NULLIF(EVAL(large_orders), 0)
6
  ) AS "Units per Large Order", /* @format number(2) */
7
  MEASURE(
8
    'large_orders',
9
    KATER__COUNT_DISTINCT(CASE WHEN shop.order_size = 'large' THEN shop.order_id END)
10
  ) AS "Large Orders", /* @format number(0) */
11
  MEASURE(
12
    'large_units',
13
    KATER__SUM(CASE WHEN shop.order_size = 'large' THEN shop.quantity END)
14
  ) AS "Units in Large Orders" /* @format number(0) */
15
FROM workspace.shop AS shop
16
GROUP BY
17
  shop.region
18
ORDER BY
19
  shop.region

EVAL(large_units) names a sibling, so it has no alias in front. A plain EVAL of a sibling works it out in the same context as the measure that reads it: here, each region.

Leave a group with no denominator empty

Large Orders per Small Order divides by Small Orders. Grouped by order size, the large row has no small orders:

1
SELECT
2
  shop.order_size,
3
  MEASURE(
4
    'large_orders',
5
    KATER__COUNT_DISTINCT(CASE WHEN shop.order_size = 'large' THEN shop.order_id END)
6
  ) AS "Large Orders", /* @format number(0) */
7
  MEASURE(
8
    'small_orders',
9
    KATER__COUNT_DISTINCT(CASE WHEN shop.order_size = 'small' THEN shop.order_id END)
10
  ) AS "Small Orders", /* @format number(0) */
11
  MEASURE(
12
    'large_per_small',
13
    EVAL(large_orders) / NULLIF(EVAL(small_orders), 0)
14
  ) AS "Large Orders per Small Order" /* @format number(2) */
15
FROM workspace.shop AS shop
16
GROUP BY
17
  shop.order_size
18
ORDER BY
19
  shop.order_size

NULLIF turns the zero into null, so the large row is empty. Without NULLIF, DuckDB gives inf for that row, and Snowflake stops the query with a division-by-zero error. With no grouping, the measure gives 1,425 divided by 1,575: 0.90.

A formula in stages

A formula can run through CTEs. Kater keeps every stage and each EVAL’s own context, so a reader still gets a measure. This semantic model works out each region’s share of all orders. The first EVAL follows the reader’s grouping, and the CLEAR() one is always the total of all orders:

1
WITH shares AS (
2
  SELECT
3
    o.region,
4
    EVAL(o.count_distinct__order_id) / NULLIF(EVAL(o.count_distinct__order_id, CLEAR()), 0) AS share
5
  FROM kater_dataset.orders AS o
6
)
7
SELECT
8
  region,
9
  MEASURE('order_share', share) AS "Share of Orders" /* @format percent(1) */
10
FROM shares
11
GROUP BY
12
  region
13
ORDER BY
14
  region

The stage has no GROUP BY. A stage that feeds a MEASURE this way cannot group, because a grouped stage fixes its values at its own grouping. With GROUP BY o.region inside shares, the compiler refuses the model: “order_share cannot be a MEASURE. The CTE shares groups its rows with GROUP BY, so its totals are fixed there.” See Change a measure’s context for CLEAR().

Total again at another grain

To average a measure over months, work it out per month in a stage that groups. Then aggregate the stage’s rows again in the measure:

1
WITH per_month AS (
2
  SELECT
3
    shop.order_month,
4
    EVAL(shop.count_distinct__order_id) AS month_orders
5
  FROM workspace.shop AS shop
6
  GROUP BY
7
    shop.order_month
8
)
9
SELECT
10
  MEASURE('avg__month_orders', KATER__AVG(per_month.month_orders)) AS "Average Orders per Month" /* @format number(2) */
11
FROM per_month

The shop has 3,000 orders in 21 months, and 3,000 divided by 21 is 142.86. Here the stage can group, because the measure aggregates the stage’s rows again with KATER__AVG.

The stage’s GROUP BY sets the grain the measure averages over. This version groups the stage by region and month, and the query by region:

1
WITH per_month AS (
2
  SELECT
3
    shop.region,
4
    shop.order_month,
5
    EVAL(shop.count_distinct__order_id) AS month_orders
6
  FROM workspace.shop AS shop
7
  GROUP BY
8
    shop.region,
9
    shop.order_month
10
)
11
SELECT
12
  per_month.region,
13
  MEASURE('avg__month_orders', KATER__AVG(per_month.month_orders)) AS "Average Orders per Month" /* @format number(2) */
14
FROM per_month
15
GROUP BY
16
  per_month.region
17
ORDER BY
18
  per_month.region

A reader of this query with no grouping gets 28.57: the average of all 105 region months, not of the 21 months. Group the stage only by what the measure averages over.

Rules

• A measure built on measures keeps their rules. Revenue per Customer reads Revenue, so each row needs one currency. Grouped by region alone, the compiler refuses it: “Revenue cannot be added up across Currency. Group by Currency or filter it to one value.” See Measures you cannot add up.
• Add up first, divide last. Put each total in its own EVAL and combine them in the formula. Never average a column of ratios.
• Divide with NULLIF. A zero denominator then gives an empty value on every warehouse.
• Several EVALs of one measure can share a formula. EVAL(m) / NULLIF(EVAL(m, CLEAR()), 0) is one output. Two EVALs of the same measure as two outputs of one SELECT both take the measure’s name, so the compiler refuses them. Declare the second with its own name.
• A stage that feeds a MEASURE directly has no GROUP BY. A stage that groups works when the MEASURE aggregates its rows again, such as with KATER__AVG.
• Siblings over a repeating join are refused. Two siblings can read different sources, and the model’s joins can repeat one’s rows for the other. A measure that combines them then fails with unsupported_measure_context. See Measures across joins and unions.

Common errors