CALCULATE_BY

Family: Calculation context

Use it for an average per customer across the whole business.

Syntax

1
CALCULATE_BY(measure, dimension, ..., summarize => 'avg' | 'sum' | 'min' | 'max')

Arguments

Returns

The combined value on each row of the result.

Usage notes

• The measure is worked out at those dimensions only. The query’s own grouping does not apply to it. Compare CALCULATE_PER, which adds the dimensions to the query’s grouping.
• The call reads one source, by simple field names. Bring a joined or computed field into a source query first.
• KATER__ALWAYS_BY writes CALCULATE_BY. A field written with one is worked out at the grouping of the query that holds it, so another query cannot read it.

Example

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

1
SELECT
2
  orders.region AS "Region",
3
  MEASURE('units_per_customer_all_regions', CALCULATE_BY(orders.sum__quantity, orders.customer_id, summarize => 'avg')) AS "Units per Customer, All Regions",
4
  MEASURE('units_per_customer_in_region', CALCULATE_PER(orders.sum__quantity, orders.customer_id, summarize => 'avg')) AS "Units per Customer, This Region"
5
FROM kater_dataset.orders AS orders
6
WHERE
7
  KATER__IS_NOT_NULL(orders.region)
8
GROUP BY
9
  orders.region
10
ORDER BY
11
  1

CALCULATE_BY works out each customer’s units in every region, then averages them. CALCULATE_PER works out each customer’s units in the row’s region only.

See also

• Change a measure’s context
• Other functions in Calculation context: TOTAL, SHARE_OF_TOTAL, VALUE_FOR, CALCULATE_ACROSS, CALCULATE_PER, RATIO, RUNNING_TOTAL, MOVING_AVERAGE, CUMULATIVE_SHARE, BY, ACROSS, IGNORE_FILTERS