Aggregate functions

Use aggregate functions when you create calculated fields so they complete calculations based on groups of rows, rather than on single rows.

Instead of using Sum or Average for a single value, take the sum or average over a row or column group or over 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. Aggregate functions include the following values:
  • Average
  • CountAll
  • CountDistinct
  • Max
  • Median
  • Min
  • Mode
  • PercentOf
  • Range
  • StdDevP
  • StdDevS
  • Sum
  • WeightedAverage
Because aggregate functions already operate on groups, their use is restricted in the following ways:
  • You can use aggregate functions only in calculated measures. Do not use aggregates to create non-measure fields.
  • You cannot add an aggregate function to a group.
  • Only AggregateFormula, Custom, or None are supported as summary calculations for aggregate functions. A Custom summary option appears in the Change Summary menu only if you defined a custom function in the Create Calculated Field dialog box.

Levels in aggregate functions

Many aggregate functions accept an optional level to specify the grouping of the aggregate. A level that is used in an aggregate must be enclosed in straight quotation marks ('), for example, 'RowGroup'. Available levels include the following options:
Current
Default. Use the current value for detail rows in a table view.
RowGroup
Use the parent values from a row location.
RowTotal
Use the grand total value from a row location.
ColumnGroup
Use the parent values from a column location.
ColumnTotal
Use the grand total value from a column location.
Total
Use the grand total value from a cross tab and the RowTotal from a table.