Using Aggregate Functions in Calculated Fields - Amazon QuickSight

Using Aggregate Functions in Calculated Fields

The aggregate functions for calculated fields in Amazon QuickSight include the following. These are only available during analysis and visualization. Each of these functions returns values grouped by the chosen dimension or dimensions. For each aggregation, there is also a conditional aggregation. These perform the same type of aggregation, based on a condition.

  • avg averages the set of numbers in the specified measure, grouped by the chosen dimension or dimensions.

  • avgIf calculates the average based on a conditional statement.

  • count calculates the number of values in a dimension or measure, grouped by the chosen dimension or dimensions.

  • countIf calculates the count based on a conditional statement.

  • distinct_count calculates the number of distinct values in a dimension or measure, grouped by the chosen dimension or dimensions.

  • distinct_countIf calculates the distinct count based on a conditional statement.

  • max returns the maximum value of the specified measure, grouped by the chosen dimension or dimensions.

  • maxIf calculates the maximum based on a conditional statement.

  • median) r Returns the median value of the specified measure, grouped by the chosen dimension or dimensions.

  • medianIf calculates the median based on a conditional statement.

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

  • minIf calculates the minimum based on a conditional statement.

  • percentile computes the nth percentile of the specified measure, grouped by the chosen dimension or dimensions.

  • 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.

  • stdevIf calculates the sample standard deviation based on a conditional statement.

  • 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.

  • stdevpIf calculates the population deviation based on a conditional statement.

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

  • varIf calculates the sample variance based on a conditional statement.

  • 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.

  • varpIf calculates the population variance based on a conditional statement.

  • sum) adds the set of numbers in the specified measure, grouped by the chosen dimension or dimensions.

  • sumIf) calculates the sum based on a conditional statement.

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

  • Custom aggregations can't contain nested aggregate functions. For example, this formula doesn't work: sum(avg(x)/avg(y)). However, nesting nonaggregated functions inside or outside aggregate functions does 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 doesn'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 .