Amazon QuickSight
User Guide

Using Aggregate Functions in Calculated Fields

You can use the following aggregate functions on calculated fields during analysis and visualization:

  • Average (avg) – Averages the set of numbers in the specified measure, grouped by the chosen dimension or dimensions.

  • Count (count) – Calculates the number of values in a dimension or measure, grouped by the chosen dimension or dimensions.

  • Distinct Count (distinct_count) – Calculates the number of distinct values in a dimension or measure, grouped by the chosen dimension or dimensions.

  • Maximum (max) – Returns the maximum value of the specified measure, grouped by the chosen dimension or dimensions.

  • Minimum (min) – Returns the minimum value of the specified measure, grouped by the chosen dimension or dimensions.

  • Sum (sum) – Adds the set of numbers in the specified measure, grouped by the chosen dimension or dimensions.

  • Sample Standard Deviation (stdev) – calculates the standard deviation of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a sample.

  • Population Standard Deviation (stdevp) – calculates the standard deviation of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a biased population.

  • Sample Variance (var) – calculates the variance of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a sample.

  • Population Variance (varp) – calculates the variance of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a biased population.

  • For each aggregation, there is also a conditional aggregation. It performs the same type of aggregation, based on a condition. Conditional aggregations include avgIf, countIf, distinct_countIf, maxIf, minIf, sumIf, stdevIf, stdevpIf, varIf, and varpIf.

When a calculated field formula contains an aggregation, it becomes a custom aggregation. To make sure your data is accurately displayed, Amazon QuickSight applies the following rules:

  • Custom aggregations can't contain nested aggregate functions. For example, this formula won't work: sum(avg(x)/avg(y)). However, nesting nonaggregated functions inside or outside aggregate functions do work. For example, ceil(avg(x)) works. So does avg(ceil(x)).

  • Custom aggregations can't contain both aggregated and nonaggregated fields, in any combination. For example, this formula won't work: Sum(sales)+quantity.

  • Filter groups can't contain both aggregated and nonaggregated fields.

  • Custom aggregations can't be converted to a dimension. They also can't be dropped into the field well as a dimension.

  • In a pivot table, custom aggregations can't be added to table calculations.

  • Scatter plots with custom aggregations need at least one dimension under Group/Color in the field wells.

For more information about supported functions and operators, see Calculated Field Function and Operator Reference for Amazon QuickSight .