NOT_ADDITIVE_ACROSS

Family: Field declarations

Write it as the last argument of MEASURE.

Syntax

1
MEASURE('name', expression, NOT_ADDITIVE_ACROSS(dimension, ...))

Arguments

Returns

Nothing of its own. It is part of the measure that holds it.

Usage notes

• Each result row must have one value of each dimension it names. Group by the dimension, filter it to one value, or SET it.
• A query that adds the measure up across the dimension does not compile. The sample shop’s Revenue gives “Revenue cannot be added up across Currency. Group by Currency or filter it to one value.”
• A measure that passes this one through, or a measure built on it, keeps the rule.
• An Excel formula column cannot add up such a measure across the dimension either. [#total spend] is refused, and SUMIF([currency], [@currency], [spend]) adds it up for each currency.

Example

This Sandbox query reads the sample shop’s orders. The table shows the rows that Kater gave.

EMEA sells in euros and in dollars. Grouped by currency, each row has one currency, so the amount adds up.

1
SELECT
2
  orders.currency AS "Currency",
3
  MEASURE('discounted_amount', KATER__SUM(orders.amount * (1 - orders.discount)), NOT_ADDITIVE_ACROSS(currency)) AS "Discounted Amount"
4
FROM kater_dataset.orders AS orders
5
WHERE
6
  KATER__IS_EQUAL_TO(orders.region, 'EMEA')
7
GROUP BY
8
  orders.currency
9
ORDER BY
10
  1

See also

• Fields
• Other functions in Field declarations: DIMENSION, MEASURE, CUSTOM_CALCULATION, EXCEL