Euros added to dollars give a number with no meaning. NOT_ADDITIVE_ACROSS(currency) on a measure says so. Then every query of the measure must hold the currency to one value per result row. The compiler refuses a query that does not. The rule stays with the measure in every model, query and measure that reads it.

How it works

In the sample shop, Revenue (sum__amount) carries NOT_ADDITIVE_ACROSS(currency). Only EMEA has orders in two currencies:

One row for EMEA would add 84,371.35 euros to 36,129.01 dollars. The rule stops that. Each result row must have one currency, and there are three ways to give it one:

A smaller unit counts too, when a relationship says that it identifies the dimension. See A smaller unit that identifies the currency.

Use the rule when a measure is right only if each group has one value of a dimension. Revenue summed across currencies is one example. A daily reach summed across days is another, because the same person counts on each day.

Syntax

1
MEASURE('name', formula, NOT_ADDITIVE_ACROSS(dimension, ...)) AS "Label"

This is Revenue in the orders Kater dataset:

1
  -- @description Order amount before discounts.
2
  MEASURE('sum__amount', KATER__SUM(orders.amount), NOT_ADDITIVE_ACROSS(currency)) AS "Revenue", /* @format currency(currency, 2) */

Add the rule to a measure

The field menu of a query writes the rule on a measure that the query declares. This query declares Large Order Revenue, the revenue from orders with an amount of 100 or more.

1
SELECT
2
  shop.region,
3
  shop.currency,
4
  -- @format currency(currency, 2)
5
  MEASURE(
6
    'large_order_revenue',
7
    SUM(CASE WHEN shop.amount >= 100 THEN shop.amount END),
8
    NOT_ADDITIVE_ACROSS(currency)
9
  ) AS "Large Order Revenue"
10
FROM workspace.shop AS shop
11
GROUP BY
12
  shop.region,
13
  shop.currency
14
ORDER BY
15
  shop.region,
16
  shop.currency

In a Kater dataset or a semantic model, the field menu has no Edit group. Write the rule in the SQL there. Your agent and the terminal use the same operation, field.not_additive_across. kater operation list PATH --field FIELD shows it for a measure that the file declares.

Examples

A query the rule refuses

Revenue by region, with nothing that holds the currency.

1
SELECT
2
  shop.region,
3
  shop.sum__amount
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.region

The compiler refuses the query with not_additive_across: “Revenue cannot be added up across Currency. Group by Currency or filter it to one value.” The EMEA row would add euros to dollars. A total with no grouping is refused for the same reason.

The quick fix, Group by Currency, adds shop.currency to the SELECT, before Revenue, and to the GROUP BY. The next example is the result.

Group by the currency

Revenue by region and currency.

1
SELECT
2
  shop.region,
3
  shop.currency,
4
  shop.sum__amount
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.region,
8
  shop.currency
9
ORDER BY
10
  shop.region,
11
  shop.currency

EMEA now has one row for each currency. Each row shows its own currency symbol, from the measure’s format.

Filter the currency to one value

Revenue by region, in dollars only.

1
SELECT
2
  shop.region,
3
  shop.sum__amount
4
FROM workspace.shop AS shop
5
WHERE
6
  shop.currency = 'USD'
7
GROUP BY
8
  shop.region
9
ORDER BY
10
  shop.region

The equality holds every row to USD, so the query can group by region alone. The result has no Currency column, but the format shows dollars, because the filter names the currency. KATER__IS_EQUAL_TO(shop.currency, 'EUR') counts too. A filter to two values, such as shop.currency IN ('USD', 'EUR'), does not, and the compiler refuses it.

Set the currency in an EVAL

Revenue by region, with one column for each currency.

1
SELECT
2
  shop.region,
3
  -- @format currency('USD', 2)
4
  MEASURE('usd_revenue', EVAL(shop.sum__amount, SET(shop.currency = 'USD'))) AS "USD Revenue",
5
  -- @format currency('EUR', 2)
6
  MEASURE('eur_revenue', EVAL(shop.sum__amount, SET(shop.currency = 'EUR'))) AS "EUR Revenue"
7
FROM workspace.shop AS shop
8
GROUP BY
9
  shop.region
10
ORDER BY
11
  shop.region

Each SET holds one evaluation to one currency, and the rows stay one per region. Only EMEA has orders in euros, so the other regions show null for EUR Revenue. See Change a measure’s context for SET and the other modifiers.

A measure built on Revenue keeps the rule

Revenue per Customer, from the shop’s region_mix query, by region group alone.

1
SELECT
2
  shop.region_group,
3
  MEASURE(
4
    'revenue_per_customer',
5
    EVAL(shop.sum__amount) / NULLIF(EVAL(shop.count_distinct__customer_id), 0)
6
  ) AS "Revenue per Customer"
7
FROM workspace.shop AS shop
8
GROUP BY
9
  shop.region_group

The compiler refuses it with not_additive_across: “Revenue cannot be added up across Currency. Group by Currency or filter it to one value.” The new measure evaluates Revenue, so it has Revenue’s rule. Group by the currency too, and the query runs:

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

The rule also reaches a query that reads region_mix. This query groups its Revenue per Customer by region group alone:

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

The compiler refuses it: “Revenue per Customer cannot be added up across Currency. Group by Currency or filter it to one value.” A pass-through keeps the rule in the same way. The workspace passes Revenue on from semantic.shop, which passes it on from the orders dataset. Each reader meets the rule.

A model that holds the currency passes Revenue on with no rule

A query with an always-applied equality on the currency. Call it usd_regions.

1
SELECT
2
  shop.region,
3
  shop.sum__amount
4
FROM workspace.shop AS shop
5
WHERE
6
  shop.currency = 'USD'
7
GROUP BY
8
  shop.region

Every row of usd_regions is in dollars, so the Revenue it passes on has no rule left. A query that reads it can total Revenue with no currency at all:

1
SELECT
2
  usd.sum__amount
3
FROM query.usd_regions AS usd

This is the sum of the five dollar rows. The result has no Currency column, so the number shows no currency symbol. See Pair the rule with a format. A workspace root with the same WHERE passes Revenue on in the same way. A REQUIRED_FILTER with an equality, such as KATER__IS_EQUAL_TO(shop.currency, 'USD'), counts too, because it always applies.

An optional filter does not count

A query with the equality as an optional filter. Call it usd_regions_optional. It groups by the currency too, because a reader can switch the filter off.

1
SELECT
2
  shop.region,
3
  shop.currency,
4
  shop.sum__amount
5
FROM workspace.shop AS shop
6
WHERE
7
  OPTIONAL_FILTER('is_usd', KATER__IS_EQUAL_TO(shop.currency, 'USD'), label='Currency is USD')
8
GROUP BY
9
  shop.region,
10
  shop.currency

With the filter off, its rows hold both currencies:

So the Revenue it passes on keeps the rule. A reader that totals it with no currency is refused:

1
SELECT
2
  usd.sum__amount
3
FROM query.usd_regions_optional AS usd

The compiler refuses it with not_additive_across: “Revenue cannot be added up across Currency. Group by Currency or filter it to one value.”

A smaller unit that identifies the currency

Revenue by order, in a model where a relationship says each order has one currency.

This semantic model starts from order events and joins each event to its order, many to one:

1
SELECT
2
  e."event id",
3
  e.order_id,
4
  e.eventtype,
5
  o.currency,
6
  o.sum__amount
7
FROM kater_dataset.order_events AS e
8
-- @label Order
9
LEFT JOIN RELATIONSHIP('order', kater_dataset.orders AS o ON e.order_id = o.order_id, 'many_to_one')

A query of its workspace, events, groups Revenue by order ID alone:

1
SELECT
2
  events.order_id,
3
  events.sum__amount
4
FROM workspace.events AS events
5
GROUP BY
6
  events.order_id
7
ORDER BY
8
  events.order_id
9
LIMIT 3

The join is many to one on order_id, so one order ID gives one order, and one currency. The result has no Currency column, so the numbers show no symbol. Group the same model by event type, and the compiler refuses it: “Revenue cannot be added up across Currency. Group by Currency or by Order ID, which identifies it, or filter it to one value.”

The compiler reads this from the relationship, never from the data. In semantic.shop, each order also has one currency. No relationship says so, and grouping Revenue by shop.order_id alone is refused. The model must publish the join’s key columns, here e.order_id.

A running total starts over for each currency

Revenue by month, with a running total for each currency.

1
SELECT
2
  KATER__MONTH(shop.order_month),
3
  shop.currency,
4
  shop.sum__amount,
5
  CUSTOM_CALCULATION(
6
    'running_sum__sum__amount',
7
    KATER__WINDOW_AGGREGATION(shop.sum__amount, 'sum', 'running') OVER (PARTITION BY shop.currency ORDER BY KATER__MONTH(shop.order_month) ASC)
8
  ) AS "Revenue (Running Sum)" /* @format currency(currency, 2) */
9
FROM workspace.shop AS shop
10
GROUP BY
11
  KATER__MONTH(shop.order_month),
12
  shop.currency
13
ORDER BY
14
  KATER__MONTH(shop.order_month),
15
  shop.currency

The first 6 of 42 rows:

A running total adds the rows before it. PARTITION BY shop.currency starts it over for each currency, so euros and dollars never meet. The shop’s monthly_revenue query partitions its running total in the same way. In the new-field form, the partition is Start over for each.

Take the partition out, and the compiler refuses the query with not_additive_across: “Revenue cannot be added up across Currency, so a total of it along the rows has to start over for each Currency. Add Currency to Start over for each (its PARTITION BY), or filter Currency to one value.” See Running totals, ranks and windows.

A share of total is out of each currency’s total

Each region’s share of Revenue in its own currency.

1
SELECT
2
  shop.region,
3
  shop.currency,
4
  shop.sum__amount,
5
  CUSTOM_CALCULATION(
6
    'share_of_total__sum__amount',
7
    KATER__SHARE_OF_TOTAL(shop.sum__amount) OVER (PARTITION BY shop.currency)
8
  ) AS "Revenue (Share of Total)" /* @format percent(1) */
9
FROM workspace.shop AS shop
10
GROUP BY
11
  shop.region,
12
  shop.currency
13
ORDER BY
14
  shop.currency,
15
  shop.region

The dollar shares add up to 100%, and EMEA has all the euros. In the new-field form, the partition is Out of the total for each. With OVER (), the compiler refuses the query: “Revenue cannot be added up across Currency, so a share or total of it has to be out of the total for each Currency. Add Currency to Out of the total for each (its PARTITION BY), or filter Currency to one value.”

Top N within each currency

The two regions with the most Revenue in each currency.

1
SELECT
2
  shop.region,
3
  shop.currency,
4
  EVAL(shop.sum__amount)
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.region,
8
  shop.currency
9
QUALIFY
10
  KATER__IS_IN_TOP(EVAL(shop.sum__amount), 2) OVER (PARTITION BY shop.currency)
11
ORDER BY
12
  shop.currency,
13
  3 DESC

The partition ranks the euro rows and the dollar rows on their own. EMEA is the only euro row, so it is the only one kept there. A rank adds nothing up, so the compiler does not ask for the partition. Without it, euro rows rank among dollar rows, as in the shop’s top_regions query.

A modifier cannot take the currency out

Revenue for each region and currency, with the currency removed from one evaluation.

1
SELECT
2
  shop.region,
3
  shop.currency,
4
  EVAL(shop.sum__amount, REMOVE(shop.currency))
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.region,
8
  shop.currency

The compiler refuses it with not_additive_across: “Revenue cannot be added up across Currency, so REMOVE cannot take Currency out. Keep Currency, or SET it to one value.” CLEAR() and SET_CUSTOM() drop the whole grouping, so the compiler refuses them too. That holds in a query and inside another MEASURE. REMOVE of another dimension is allowed. EVAL(shop.sum__amount, REMOVE(shop.region)) gives each region’s currency total.

Pair the rule with a format

currency(currency, 2) shows each row in its own currency. It reads the symbol from the Currency column of the same result. The rule makes sure each row has one currency to read. Write it above the measure, or after it as the orders dataset does:

1
  -- @format currency(currency, 2)
2
  MEASURE('sum__amount', KATER__SUM(orders.amount), NOT_ADDITIVE_ACROSS(currency)) AS "Revenue",

• The column is in the result. Each row shows its own symbol, such as $105,459.33 and €84,371.35.
• A filter holds the currency to one value. The result shows that currency’s symbol, though the column is not there.
• Neither. The result shows the number with no symbol, such as 79.96.

See Formats, descriptions and AI context.

Keep the rule with a test

A shared test can keep a refused query, so the rule is never lost. If someone takes the rule off Revenue, the test fails. Save the refused query as revenue_by_region.sql, with FROM semantic.shop AS shop in place of the workspace, then run:

kater new test revenue_needs_currency --model semantic.shop \
  --sql-file revenue_by_region.sql --refused --shared

It compiles the SQL and keeps the code the compiler refuses it with. The test goes in the shared tests of the model, modeling/kater_objects/tests/shop.sql:

1
---
2
name: revenue_needs_currency
3
model_type: test
4
expect:
5
  refused: not_additive_across
6
---
7

8
SELECT
9
  shop.region,
10
  shop.sum__amount
11
FROM semantic.shop AS shop
12
GROUP BY
13
  shop.region

The readiness check Every rule pinned asks for one such test for each rule. See Tests.

Where you see the rule

A measure’s definition card shows the rule under Inputs, as Required. Open the Definitions view, or run kater model explain sum__amount --relation semantic.shop. Revenue’s card says:

Rules

• The rule goes on a measure. Only a MEASURE takes NOT_ADDITIVE_ACROSS, as its last argument.
• Name dimensions by field name. Write currency, not the label. A label can change, and the rule still holds.
• One value for each result row. Group by the dimension, filter it with an equality to one value, or SET it in the EVAL. A filter to several values does not count.
• Every reader meets the rule. A pass-through keeps it, and so does a measure built on the measure. A query that reads another query meets it too.
• An always-applied equality ends the rule. A WHERE or REQUIRED_FILTER that holds the dimension to one value passes the measure on with no rule. An optional filter does not.
• A relationship can stand in for the dimension. A many to one join matches on a key. Grouping by that key holds the joined side’s columns to one value.
• A sum along the rows keeps the dimension in its partition. Running totals and shares start over, or are out of the total, for each value.
• A distinct count needs no rule. Customers by region add up to 976, and the shop has 240 customers. Kater counts Customers again from the rows at each grouping, and never adds the regions’ values.

Common errors