MEASURE

Family: Field declarations

A measure can be one aggregate, or a formula of other measures.

Syntax

1
MEASURE('name', expression [, NOT_ADDITIVE_ACROSS(dimension, ...)]) AS "Label"

Arguments

Returns

A measure. A reader that groups by other dimensions gets it worked out again at their grouping.

Usage notes

• Write it only in the model’s final SELECT, as a whole column.
• A formula of measures is worked out again from each measure. Units per order is the units divided by the orders of each group, not an average of the rows’ ratios.
• A window is never a measure. Declare it with CUSTOM_CALCULATION.
• To use the same measure twice in one SELECT, such as once as it is and once with CLEAR(), declare the second one with its own name.

Example

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

1
SELECT
2
  orders.channel AS "Channel",
3
  MEASURE('units_per_order', EVAL(orders.sum__quantity) / EVAL(orders.count_distinct__order_id)) AS "Units per Order"
4
FROM kater_dataset.orders AS orders
5
GROUP BY
6
  orders.channel
7
ORDER BY
8
  1

See also

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