CALCULATE_PER

Family: Calculation context

Use it for an average per customer in each region.

Syntax

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

Arguments

Returns

The combined value on each row of the result.

Usage notes

• The query’s own grouping applies, and the dimensions are added to it. Compare CALCULATE_BY, which uses only the dimensions it names.
• The call reads one source, by simple field names. Bring a joined or computed field into a source query first.
• KATER__CALCULATE_PER writes CALCULATE_PER. 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_in_region', CALCULATE_PER(orders.sum__quantity, orders.customer_id, summarize => 'avg')) AS "Units per Customer, This Region",
4
  MEASURE('units_per_customer_all_regions', CALCULATE_BY(orders.sum__quantity, orders.customer_id, summarize => 'avg')) AS "Units per Customer, All Regions"
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

See also

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