Amazon QuickSight
User Guide

The AWS Documentation website is getting a new look!
Try it now and let us know what you think. Switch to the new look >>

You can return to the original look by selecting English in the language selector above.

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 .