Tableau Essentials: Calculated Fields - Aggregate Functions

Not everyone is a Tableau guru, at least not yet. To help Tableau rookies, we’re starting from square one with the Tableau Essentials blog series. The series is intended to be an easy-to-read reference on the basics of using Tableau Software, particularly Tableau Desktop. Since there are so many cool features to cover in Tableau, the series will include several different posts. 

In this article on Tableau Functions, we’ll examine Aggregate Functions. Aggregate Functions are a type of function where values of multiple rows are grouped together as the input to form a single value of more significant meaning, such as a set or list.

To access these functions, simply select Aggregate from the function drop-down list on the Create Calculated Field window. If you’d like to revisit some of the previous Tableau Essentials articles we’ve released on the other types of functions, you can visit them in the links at the end of the post.

Now, let’s dive right into the different types of Aggregate Functions with some examples. Most of these functions will be very familiar to anyone with Excel experience.

ATTR Function

ATTR(expression)

The ATTR function, short for attribute, returns a value if all of the rows have a single value. If all of the rows do not match, it will return a value of “*”. Null values are ignored. Here’s an example:

ATTR([City]) = New York City

AVG Function

AVG(expression)

The AVG function returns the average of all values in the expression. AVG can only be used with fields that are numeric. Null values are ignored. Here’s an example:

AVG([Profit]) = 156

COUNT Function

COUNT(expression)

Count will return the number of items in a group. Null values are not counted. Here’s an example:

COUNT(‘OrderID’) = 52,909

COUNTD Function

COUNTD(expression)

The CountD function will return distinct items in a group. Null values are not counted. Each unique item is counted only once. This function is not available for workbooks that were created before Tableau Desktop v8.2, workbooks that use MS Excel or text files as a data source, workbooks that use legacy connections and workbooks that use MS Access data sources. Here’s an example:

COUNTD(‘State’) = 50

MAX Function

MAX(expression)

The MAX function returns the maximum of a single expression across all records or the maximum of two expressions for each record. The two arguments must be the same type. This function will return a value of NULL if either argument is NULL. Here’s an example:

MAX([Age]) = 91

MEDIAN Function

MEDIAN(expression)

This function returns the median of a single expression, and it can only be used with numeric fields. Null values are ignored. The median is the middle number of a sequence. Here’s an example:

MEDIAN(4,7,15,27,100) = 15

MIN Function

MIN(expression)

Similar to the MAX function, the MIN function returns the minimum of a single expression across all records or the minimum of two expressions for each record. MIN returns a value of NULL if either of the two arguments is NULL. The two arguments must be of the same type. Here’s an example:

MIN([Age]) = 1

PERCENTILE Function

PERCENTILE(expression, number)

The Percentile function will return the percentile expression corresponding to the given number. Values that you can use for the number are between 0 and 1. If you use 0.50, then it will always return the median number. Here’s an example:

PERCENTILE([PROFIT],0.66) = 43.66

STDEV Function

STDEV(expression)

The STDEV function stands for standard deviation and is a statistical function. It will return the statistical standard deviation for all the values in a given expression based on a sample of the population.

STDEVP Function

STDEVP(expression)

The STDEVP function is similar to the statistical standard deviation function above, but it will instead return the statistical standard deviation for all of the values in a given expression based on a biased population.

SUM Function

SUM(expression)

Very simply, this function sums all of the values in an expression. Here’s an example:

SUM(4,5,6) = 15

VAR Function

VAR(expression)

Another statistical function, VAR will return the statistical variance of all the values in a given expression based on a sample of the population.

VARP Function

VARP(expression)

As above, but the VARP function will return the statistical variance of all the values in a given expression based on the entire population.

Calculated Fields

Calculated fields can add a whole new layer of insight to your Tableau dashboards. The possibilities are practically endless, but we’ll be covering the fundamentals, especially functions, to help you build a foundational understanding of how and when to use them. Check back for more posts covering:

Introduction

  1. Logical Functions
  2. Number Functions
  3. Date Functions
  4. String Functions
  5. Type Conversion
  6. Aggregate Functions
  7. User Functions

Another great resource for functions is Dan Murray's best-selling guidebook, "Tableau Your Data!" It features a whole section devoted the functions we'll be covering in this series and much, much more.

More Tableau Essentials

Want to learn more about Tableau? We have several posts outlining all of Tableau's fantastic features. Check out the full list on our Tableau Essentials blog channel.

As always, let us know if you have any questions or comments about this post or Tableau in general. If you're looking for personalized training or help with something bigger, contact us directly!

More "Tableau Essentials"

More from the Author

Carly Capitula

Analytics Consultant | Enablement Practice Lead