Aggregate functions - Amazon QuickSight

Important: We've redesigned the Amazon QuickSight analysis workspace. You might encounter screenshots or procedural text that doesn't reflect the new look in the QuickSight console. We're in the process of updating screenshots and procedural text.

To find a feature or item, use the Quick search bar.

For more information on QuickSight's new look, see Introducing new analysis experience on Amazon QuickSight.

Aggregate functions

Aggregate functions 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.

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 .

The aggregate functions for calculated fields in QuickSight include the following.