In Tableau, aggregate functions allow us to reduce or change the granularity of our data. As such, they are used regularly when building calculations and dashboards.
This blog aims to breakdown and explain many of these aggregate functions. However, this post will not contain aggregate functions more aligned with ‘Spatial’ or ‘Level of Detail’ calculations
A list and breakdown of Aggregate (and all) functions can also be found by selecting the ‘▶’ found on the right hand side of the Calculated Field pop-up.
ATTR
Syntax:
ATTR (expression)
Definition:
Returns the value of the expression if it has a single value across all rows in the current level of detail. If there is more than one value, Tableau returns an asterisk (*). Null values are ignored.
Example:
ATTR ([Region])
Explanation:
This function indicates whether there are multiple values, but only one was expected. To try and explain it in the simplest form its whether MIN=MAX. If they are the same the value is returns. If not the function will return an asterisk (*).
AVG
Syntax:
AVG (expression)
Definition:
Returns the arithmetic mean of all values in the expression. Only works with numeric fields. Nulls are ignored.
Example:
AVG ([Profit])
Explanation:
Returns the mean of all the selected (numeric) fields.
COUNT
Syntax:
COUNT(expression)
Definition:
Returns the number of non-null values in the expression.
Example:
COUNT([Customer ID])
Explanation:
Useful when you want to know how many records exist within a field, excluding null values.
COUNTD
Syntax:
COUNTD(expression)
Definition:
Returns the number of distinct (unique) values in the expression. Each value is only counted once. Nulls are ignored.
Example:
COUNTD([Region])
Explanation:
Can be thought of as “How many unique customers do we have?”
CORR
Syntax:
CORR(expr1, expr2)
Definition:
Returns the Pearson correlation coefficient between two expressions.
Example:
CORR([Sales], [Profit])
Explanation:
Pearson’s correlation coefficient measures the strength and direction of the linear relationship between two numeric variables, returns a value between -1 and 1.
COVAR
Syntax:
COVAR(expr1, expr2)
Definition:
Returns the sample covariance of two expressions.
Example:
COVAR([Sales], [Profit])
Explanation:
Covariance shows whether two variables tend to move together in the same direction, however, unlike correlation, it is not standardized and can be harder to interpret on its own.
COVARP
Syntax:
COVARP(expr1, expr2)
Definition:
Returns the population covariance of two expressions.
Example:
COVARP([Sales], [Profit])
Explanation:
Similar to COVAR but used when your data represents the entire population rather than a sample.
MAX
Syntax:
MAX (expression)
Definition:
Returns the maximum value of an expression across all records.
Example:
MAX([Sales])
Explanation:
Used to identify the ‘peak’ value (e.g., highest sales or latest dates).
MEDIAN
Syntax:
MEDIAN(expression)
Definition:
Returns the median value of a numeric expression. Nulls are ignored.
Example:
MEDIAN([Profit])
Explanation:
The median represents the middle value in a sorted list and is often preferred over averages when data contains outliers.
MIN
Syntax:
MIN(expression)
Definition:
Returns the minimum value of an expression across all records.
Example:
MIN([Profit])
Explanation:
Used to identify the lowest value (e.g., minimum sales or earliest dates)
PERCENTILE
Syntax:
PERCENTILE(expression, number)
Definition:
Returns the value at the specified percentile of the expression. The percentile is expressed as a decimal between 0 and 1.
Example:
PERCENTILE([Sales], 0.90)
Explanation:
This function is useful for understanding how a measure is distributed, such as identifying the top 10% of sales values.
STDEV
Syntax:
STDEV(expression)
Definition:
Returns the sample standard deviation of the expression.
Example:
STDEV([Profit])
Explanation:
Standard deviation measures how spread out values are around the mean, helping quantify variability in your data.
STDEVP
Syntax:
STDEVP(expression)
Definition:
Returns the population standard deviation of the expression.
Example:
STDEVP([Profit])
Explanation:
Similar to STDEV but used when your data represents the entire population rather than a sample.
SUM
Syntax:
SUM(expression)
Definition:
Returns the sum of all values in the expression. Numeric fields only. Nulls are ignored.
Example:
SUM([Profit])
Explanation:
A commonly used aggregate in Tableau, SUM simply returns the total sum of an expression.
VAR
Syntax:
VAR(expression)
Definition:
Returns the sample variance of the expression.
Example:
VAR([Profit])
Explanation:
Variance measures how far values are spread from the mean.
VARP
Syntax:
VARP(expression)
Definition:
Returns the population variance of the expression.
Example:
VARP([Profit])
Explanation:
Similar to VAR but used when your data represents the entire population rather than a sample.
Hopefully, you should now have an understanding of how these aggregate functions can be used within Tableau. However, if you ever get stuck then you can always pop back and look over this blog again.