A measure is a named aggregate, such as Revenue or Orders. Kater does not store its value. Each query that uses it works it out again from the source rows, at that query’s grouping and filters. A chart shows its query’s rows as they are. So the same measure gives the right number in every query, chart, app and agent answer that reads it.

How it works

The context decides the number

A measure’s context is the grouping and the filters of the place that uses it. Orders with no grouping counts every order of the sample shop:

1
SELECT
2
  shop.count_distinct__order_id
3
FROM workspace.shop AS shop

The same measure grouped by region gives one number per region:

1
SELECT
2
  shop.region,
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.region
7
ORDER BY
8
  shop.region

Nothing about Orders changed between the two queries. Only the context changed.

Totals come from the rows, never from other totals

Customers counts distinct customers. Grouped by region, it gives these rows:

1
SELECT
2
  shop.region,
3
  shop.count_distinct__customer_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.region
7
ORDER BY
8
  shop.region

The rows add up to 976, but the shop has 240 customers. Most customers ordered in more than one region, so each of them counts once in every region they ordered in. A query of Customers with no grouping gives 240, because Kater counts the distinct customers again from the order rows:

1
SELECT
2
  shop.count_distinct__customer_id
3
FROM workspace.shop AS shop

So a model with measures is like a function, not a table. Its grouped rows are a preview of one run. A reader that adds up those rows gets a wrong number, so Kater never does that. It works each measure out again from the source rows, in the reader’s context.

Try it: How measures work lets you change a query and see which orders each number reads.

Syntax

1
-- @description What one unit of the measure is.
2
-- @format number(0)
3
MEASURE('name', formula, NOT_ADDITIVE_ACROSS(dimension, ...)) AS "Label"

• The formula aggregates rows or evaluates measures. KATER__SUM(orders.amount) aggregates rows. EVAL(shop.sum__amount) / NULLIF(EVAL(shop.count_distinct__customer_id), 0) evaluates two measures. A formula that does neither is a dimension.
• Most measures come from the Aggregations menu. It writes the declaration, the name and the format for you. Write a measure by hand only for a formula that no menu writes, such as a ratio. See Measures built from measures.

Aggregations

The Aggregations menu on a field writes one of these functions. The values are for the whole sample shop, with no grouping. Revenue is the exception: it needs one currency per row, so it has one value per currency.

• Count and Count Distinct differ when a value repeats. Each of the 3,000 orders has a customer, so Customer ID (Count) is 3,000. Only 240 of those customers are different, so Customer ID (Count Distinct) is 240. Both skip nulls: Region (Count) is 2,936 and Region (Count Distinct) is 4, because 64 orders have no region.
• The menu offers what fits the field. A number marked @continuous, such as Amount, offers Count, Count Distinct, Min, Max, Sum, Average and List. A number marked @categorical, such as Quantity, offers Count, Count Distinct and List. A text field offers Count, Count Distinct, Min, Max and List, and so does a date marked @categorical or @continuous. A number or a date with neither mark offers only List. A true or false field offers Count True and Count False.
• A label names the field and the operation. The operation labels its measure with the field’s label, such as “Customer ID (Count Distinct)”. Change the label to what people say, such as “Customers”.
• Plain SQL aggregates work too. MEASURE('total_quantity', SUM(shop.quantity)) gives 9,154, and COUNT(DISTINCT shop.region) gives 4. The KATER__ form is what the menus write. A field written with it reopens in its operation’s form, and the compiler turns it into your warehouse’s own SQL.

Examples

Revenue, held to one currency

The orders Kater dataset declares Revenue as a sum of the order amount. Its rule says that it cannot be added up across currencies:

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

A query of Revenue by region and currency:

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

Each row holds one currency, so the rule holds, and the format shows each row in its own currency. EMEA has two rows because it sells in euros and in dollars. A query that drops the currency is refused. See Measures you cannot add up.

Customers, a count of distinct values

The customers Kater dataset declares Customers:

1
  MEASURE('count_distinct__customer_id', KATER__COUNT_DISTINCT(customers.customer_id)) AS "Customers"

Grouped by each customer’s loyalty tier:

1
SELECT
2
  shop."loyalty tier",
3
  shop.count_distinct__customer_id,
4
  shop.count_distinct__order_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop."loyalty tier"
8
ORDER BY
9
  shop."loyalty tier"

These rows add up to 240, because each customer has one tier. By region they add up to 976, because a customer can order in several regions. In both queries each number is right for its row, and Kater never adds the rows up.

Average Discount, and the average of averages

The orders Kater dataset declares Average Discount:

1
  MEASURE('avg__discount', KATER__AVG(orders.discount)) AS "Average Discount"

The semantic model of the sample shop does not pass Average Discount through. This example adds o.avg__discount to the semantic model’s SELECT, then groups it by region:

1
SELECT
2
  shop.region,
3
  shop.avg__discount,
4
  shop.count_distinct__order_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.region
8
ORDER BY
9
  shop.region

The average of these five rows is 0.0597, but the shop’s Average Discount is 0.0581. The measure divides the sum of all 3,000 discounts by 3,000. An average of the five rows gives the 64 orders with no region the same weight as the 777 orders in EMEA. Kater works the average out again from the order rows, so a reader always gets 0.0581.

A filtered measure, two ways

Web orders can be a measure of its own in the model, or Orders with a different context in a reader. This conditional aggregate in the semantic model counts only the web orders:

1
  MEASURE(
2
    'web_orders',
3
    KATER__COUNT_DISTINCT(CASE WHEN o.channel = 'web' THEN o.order_id END)
4
  ) AS "Web Orders"

A query that reads it:

1
SELECT
2
  shop.region,
3
  shop.web_orders
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.region
7
ORDER BY
8
  shop.region

A query that sets the channel of Orders instead, with no new measure in the model:

1
SELECT
2
  shop.region,
3
  EVAL(shop.count_distinct__order_id, SET(shop.channel = 'web'))
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.region
7
ORDER BY
8
  shop.region

Both queries give the same rows:

In the editor

This measure is the largest discount of any order in the group. It reads the Discount field of the orders Kater dataset.

1
  MEASURE('max__discount', KATER__MAX(orders.discount)) AS "Largest Discount"

In a workspace query, right-click a field in the field list. Aggregations is one of its categories. In the dialog, the Turn it into step names the aggregations Total, Average, Count, Count unique, Smallest, Largest and List. See Queries.

Where measures live

Tests and Sandbox scratch files can declare measures too. See How modeling works for the layers. A window is never a measure: a value worked out over a query’s result rows is a custom calculation.

Rules

• A measure aggregates. Its formula aggregates rows, or evaluates other measures with EVAL. A constant or one row’s value is a dimension.
• A window is never a measure. A reader works a measure out one group at a time. A window inside it would see only that group. Use CUSTOM_CALCULATION in a query, or DIMENSION in a shared model.
• A new measure needs a new name. It cannot take the name of any field in scope, in any letter case. Labels are not names, so two fields can share a label.
• A measure is never aggregated again. SUM(shop.sum__quantity) would repeat each group’s total on every source row and add up the copies. Select the measure on its own for its total.
• One value of a row is not a total. A Min or Max of a date, a text or an ID that you write by hand, such as MAX(user_id), keeps one value of each group. The compiler warns with dimension_like_measure and offers to declare a dimension. Keep the measure only when “the latest order date per group” is the number you want. A Min, Max or List from the Aggregations menu gets no warning, because you selected it as a measure.

Common errors

Next

• How measures work: an interactive guide on the sample shop.
• Measures built from measures: ratios and formulas that stay right at every grouping.
• Change a measure’s context: the grand total, another member or another period beside the rows you show.
• Pass through, evaluate or declare: what readers of your model get when it reads a measure.
• Measures you cannot add up: rules such as one currency per row.
• Measures across joins and unions: how a measure counts its own rows once.