Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Reports & Analytics

Aggregate functions in calculated fields and measures

Aggregate functions in calculated fields perform calculations 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 may want to take the sum or average over a row or column group or over the total data set.
These are the available aggregate functions:
  • Average
  • CountAll
  • CountDistinct
  • Max
  • Median
  • Min
  • Mode
  • PercentOf
  • Range
  • StdDevP
  • StdDevS
  • Sum
  • WeightedAverage

Restrictions

Because aggregate functions already operate on groups, their use is restricted in the following ways:
  • You can use aggregate functions only in calculated measures. Aggregates shouldn't be used to create non-measure fields.
  • You can't add an aggregate function to a group.
  • You shouldn't use an aggregate function as a filter.
  • The only supported summary calculations for aggregate functions are: AggregateFormula, Custom, and None.

Levels

Many aggregate functions accept an optional level to specify the grouping of the aggregate. A level used in an aggregate must be enclosed in straight quotes (;); for example, 'RowGroup.'
The available levels are:
  • Current (default): use the current value when looking at 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 Crosstab and the RowTotal from a Table

This article applies to:

  • PRODUCT: ONESOURCE Determination,ONESOURCE Indirect Tax

Related Content