Tableau Essentials: Calculated Fields – Aggregate Functions

Data

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!

Want More Tableau Essentials

  1. Tableau Essentials: Chart Types – Introduction
  2. Tableau Essentials: Chart Types – The Text Table
  3. Tableau Essentials: Chart Types – Heat Map
  4. Tableau Essentials: Chart Types – Highlight Table
  5. Tableau Essentials: Chart Types – Symbol Map
  6. Tableau Essentials: Chart Types – Filled Map
  7. Tableau Essentials: Chart Types – Pie Chart
  8. Tableau Essentials: Chart Types – Horizontal Bar Chart
  9. Tableau Essentials: Chart Types – Stacked Bar Chart
  10. Tableau Essentials: Chart Types – Side-by-Side Bar Chart
  11. Tableau Essentials: Chart Types – Treemap
  12. Tableau Essentials: Chart Types – Circle View
  13. Tableau Essentials: Chart Types – Side-by-Side Circle View
  14. Tableau Essentials: Chart Types – Line Charts (Continuous & Discrete)
  15. Tableau Essentials: Chart Types – Dual-Line Chart (Non-Synchronized)
  16. Tableau Essentials: Chart Types – Area Charts (Continuous & Discrete)
  17. Tableau Essentials: Chart Types – Scatter Plot
  18. Tableau Essentials: Chart Types – Histogram
  19. Tableau Essentials: Chart Types – Box-and-Whisker Plot
  20. Tableau Essentials: Chart Types – Gantt Chart
  21. Tableau Essentials: Chart Types – Bullet Graph
  22. Tableau Essentials: Chart Types – Packed Bubbles
  23. Tableau Essentials: Formatting Tips – Introduction
  24. Tableau Essentials: Formatting Tips – Custom Shapes
  25. Tableau Essentials: Formatting Tips – Labels
  26. Tableau Essentials: Formatting Tips – Color
  27. Tableau Essentials: Formatting Tips – Tooltips
  28. Tableau Essentials: Formatting Tips – Maps
  29. Tableau Essentials: Calculated Fields – Introduction
  30. Tableau Essentials: Calculated Fields – Logical Functions
  31. Tableau Essentials: Calculated Fields – Number Functions
  32. Tableau Essentials: Calculated Fields – Date Functions
  33. Tableau Essentials: Calculated Fields – String Functions
  34. Tableau Essentials: Calculated Fields – Type Conversion
  35. Tableau Essentials: Calculated Fields – Aggregate Functions
  36. Tableau Essentials: Calculated Fields – User Functions

More About the Author

Carly Capitula

Principal / Enablement Practice Lead
A Quick Recap of the 2017 DRIVE/ Conference Last week, some of the InterWorks team dropped by Seattle, WA for DRIVE/ 2017 – a conference for professionals in fundraising and ...
Tableau Viz Showcase by Industry: K-12 Education “Data-driving decision making” is a phrase heard in several industries, perhaps none more so than in education. In such a scrutinized ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK