Aggregate functions in calculated fields perform a calculation based on groups of rows, rather than on single rows. For example, it doesn't make sense to use Sum or Average on a single value; instead, you want to take the sum or average of a row or column group, or of the total set. In many cases, aggregate functions in the Ad Hoc Editor are analogous to SQL functions that can be used with the GROUP BY clause in a SELECT statement.
The aggregate functions are as follows:
Average CountAll | MedianMin | Range StdDevP | WeightedAverage |
CountDistinctMax | Mode PercentOf | StdDevsSum | |
Aggregate functions already operate on groups, so their use is restricted in the following ways:
You can only use aggregate functions in calculated measures—aggregates should not be used to create non-measure fields.
You cannot add an aggregate function to a group.
You should not use an aggregate function as a filter.
Only "AggregateFormula," "Custom," or "None," are supported as summary calculations for aggregate functions.
"Custom" only appears in the Change Summary right-click menu if you have defined a custom function in the Create Calculated Field dialog box.