This article introduces aggregate functions and their uses in Tableau. It also demonstrates how to create an aggregate calculation using an example.
Aggregate functions allow you to summarize or change the granularity of your data.
For example, you might want to know exactly how many orders your store had for a particular year. You can use the COUNTD function to summarize the exact number of orders your company had, and then break the visualization down by year.
The calculation might look something like this:
COUNTD(Order ID)
The visualization might look something like this:
The following aggregate functions are available in Tableau for you to use:
ATTR - Returns the value of the expression if it has a single value for all rows. Otherwise returns an asterisk.
AVG - Returns the average of all the values in the expression.
COLLECT - An aggregate calculation that combines the values in the argument field. Can only be used with spatial fields.
CORR - Returns the Pearson correlation coefficient of two expressions.
COUNT - Returns the number of items in a group.
COUNTD - Returns the number of distinct items in a group.
COVAR - Returns the sample covariance of two expressions.
COVARP - Returns the population covariance of two expressions.
MAX - Returns the maximum of an expression across all records.
MEDIAN - Returns the median of an expression across all records.
MIN - Returns the minimum of an expression across all records.
PERCENTILE - Returns the percentile value from the given expression corresponding to the specified number.
STDEV - Returns the statistical standard deviation of all values in the given expression based on a sample of the population.
STDEVP - Returns the statistical standard deviation of all values in the given expression based on a biased population.
SUM - Returns the sum of all values in the expression.
VAR - Returns the statistical variance of all values in the given expression based on a sample of the population.
VARP - Returns the statistical variance of all values in the given expression on the entire population.
For more detailed definitions of these functions, as well as syntax and examples, see Tableau Functions (by Category) and select Aggregate functions. There are also several built in aggregations in Tableau. For more information, see Aggregations.
Follow along with the steps below to learn how to create an aggregate calculation.
In Tableau Desktop, connect to the Sample - Superstore saved data source, which comes with Tableau.
Navigate to a worksheet and select Analysis > Create Calculated Field.
In the calculation editor that opens, do the following:
Name the calculated field Margin.
Enter the following formula:
IIF(SUM([Sales]) !=0,
SUM([Profit])/SUM([Sales]), 0)
Note: You can use the function reference to find and add aggregate functions and other functions (like the logical IIF function in this example) to the calculation formula. For more information, see Use the functions reference in the calculation editor.
When finished, click OK.
The new aggregate calculation appears under Measures in the Data pane. Just like your other fields, you can use it in one or more visualizations.
Note: Aggregation calculations are always measures.
When Margin is placed on a shelf or card in the worksheet, its name is changed to AGG(Margin), which indicates that it is an aggregate calculation and cannot be aggregated any further.
The rules that apply to aggregate calculations are as follows:
For any aggregate calculation, you cannot combine an aggregated value and a disaggregated value. For example, SUM(Price)*[Items] is not a valid expression because SUM(Price) is aggregated and Items is not. However, SUM(Price*Items) and SUM(Price)*SUM(Items) are both valid.
Constant terms in an expression act as aggregated or disaggregated values as appropriate. For example: SUM(Price*7) and SUM(Price)*7 are both valid expressions.
All of the functions can be evaluated on aggregated values. However, the arguments to any given function must either all be aggregated or all disaggregated. For example: MAX(SUM(Sales),Profit) is not a valid expression because Sales is aggregated and Profit is not. However, MAX(SUM(Sales),SUM(Profit)) is a valid expression.
The result of an aggregate calculation is always a measure.
Like predefined aggregations, aggregate calculations are computed correctly for grand totals. Refer to Grand Totals for more information.
Understanding Calculations: Aggregate Calculations
Tableau Functions (by Category)
Tableau Functions (Alphabetical)