Amazon QuickSight
User Guide

 AWS Documentation » Amazon QuickSight » User Guide » Working with Analyses » Adding a Calculated Field to an Analysis » Using Aggregate Functions in Calculated Fields

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 .

Topics