Group aggregation functions

Learn about group aggregation functions, or pinned measures.

What if you want to aggregate a value by a specific attribute (for example, show revenue by product)? This is known as a grouped aggregation, but some people call it a pinned measure or level-based measure. You can do this for any aggregation using the grouping functions.

Each of the grouping functions accepts a measure and one or more optional attributes:

formula (measure, [attribute, attribute, ...])

Only the measure value is required. If you supply both a measure and an attribute, the function returns the aggregate of the measure grouped by the attribute(s). You should experiment with only a measure and then with an attribute to see which output best meets your use case.

Pivot tables do not support use of group aggregation functions.

List of group functions

Group aggregation functions have names with formats like group_<aggregation>. The group aggregation functions are the following:

group_average

Takes a measure and one or more attributes. Returns the average of the measure grouped by the attribute(s).

group_average (revenue, customer region)

group_count

Takes a measure and one or more attributes. Returns the count of the measure grouped by the attribute(s).

group_count (revenue, customer region)

group_max

Takes a measure and one or more attributes. Returns the maximum of the measure grouped by the attribute(s).

group_max (revenue, customer region)

group_min

Takes a measure and one or more attributes. Returns the minimum of the measure grouped by the attribute(s).

group_min (revenue, customer region)

group_stddev

Takes a measure and one or more attributes. Returns the standard deviation of the measure grouped by the attribute(s).

group_stddev (revenue, customer region)

group_sum

Takes a measure and one or more attributes. Returns the sum of the measure grouped by the attribute(s).

group_sum (revenue, customer region)

group_unique_count

Takes a column name and one or more attributes. Returns the number of unique values in a column, grouped by the attribute(s).

group_unique_count ( product, supplier)

group_variance

Takes a measure and one or more attributes. Returns the variance of the measure grouped by the attribute(s).

group_variance (revenue, customer region)

Flexible aggregation

The group_aggregate function gives you more control over aggregation and filtering.

See Flexible aggregation to learn more about specifying query_groups with this formula.



Was this page helpful?