 Select Page

Aggregate Functions

Aggregate functions are special functions which summarize columns from lower levels. Aggregate functions can be used on on Grouped levels or in a Total column. For example, they can be used to Sum all of the values of a column, or Count all of the values for each grouping of a column. As such, aggregates must always refer to columns in a level lower than the result.

If the column being aggregated is grouped, then the aggregate is computed for each group. For example, if the worksheet is grouped by the column [Year], then placing Sum([Revenue]) on the [Year] level computes the revenue for each year.

The results of aggregate functions can be referenced by lower levels. An example would be to create a Sum([Amount]) column as Total and then an [Amount] / [Total] column as Percentage. This converts each Amount value into a relative percentage value.

Calculate the average of a column of numbers.
Calculate the Pearson correlation coefficient, also known as the bivariate correlation, of two columns.
Count the non-Null values within a column or group.
Counts the number of unique values within a column or group.
Counts the number of rows in a table or group for which all given conditions are true.
Find the maximum value for a column or group.
Find the median of a column or group.
Find the minimum value for a column or group.
Counts the number of unique values within a column or group. This is an alias of the CountDistinct function and works the same way.
Calculate the continuous k-th percentile value for a column or group.
Find the discrete k-th percentile value for a column or group.
Compute the standard deviation of a column or group.
Sum the values in a column or group.
Sum the values in a column if all the conditions are true.
Returns the sum of the product of values in a series of columns.
Compute the statistical variance of a column or group. We are rewriting the rules of analytics. Sigma empowers domain experts to join the data conversation, answer the toughest questions, and drive insights.