Tableau Aggregate Functions


image


Sometimes it is useful to look at numerical data in an aggregated form such as a summation or an average. The mathematical functions that produce aggregated data are called aggregation functions. Aggregation functions perform a calculation on a set of values and return a single value.


You can use Tableau to set an aggregation only for measures in relational data sources. Multidimensional data sources contain aggregated data only.

Aggregation defines how values are expressed. Most Tableau functions are calculated at the database server with only the results being sent to Tableau. If you are familiar with SQL, you will find most of the functions in the Tableau are an extension of SQL. Tableau uses the Sum aggregation by default. If the default aggregation isn’t what you want, point at the pill of the measure you’ve placed into the view—right-click, and select more appropriate aggregation functions. 

Tableau Functions

Sum – Returns the sum of the numbers in a measure. Null values are ignored.

Average – Returns the arithmetic mean of the numbers in a measure. Null values are ignored.

Median – Returns the median of the numbers in a measure. Null values are ignored.

Count – Returns the number of rows in a measure or a dimension. When applied to a dimension, Tableau creates a new temporary column that is a measure because the result of a count is a number. You can count numbers, dates, Booleans, and strings. Null values are ignored in all cases

Count Distinct – Returns the number of unique values in a measure or dimension. When applied to a dimension, Tableau creates a new temporary column that is a measure because the result of a count is a number. You can count numbers, dates, Booleans, and strings. Null values are ignored in all cases.

Minimum – Returns the smallest number in a measure or continuous dimension. Null values are ignored.

Maximum – Returns the largest number in a measure or a continuous dimension. Null values are ignored.

Standard Deviation – Returns the standard deviation of all values in the given expression based on a sample population. Null values are ignored. Returns a Null if there are fewer than 2 members in the sample that are not Null. Use this function if your data represents a sample of the population.

Standard Deviation of a Population – Returns the standard deviation of all values in the given expression based on a biased population. Assumes that its arguments consist of the entire population. Use this function for large sample sizes.

Variance – Returns the variance of all values in the given expression based on a sample. Null values are ignored. Returns a Null if there are fewer than 2 members in the sample that are not Null. Use this function if your data represent a sample of the population.

Variance of a Population – Returns the variance of all values in the given expression based on a biased population. Assumes that its arguments consist of the entire population. Use this function for large sample sizes.

These are clearly defined in Tableau’s online manual. Search the help menu to read more about each of them if you are unfamiliar with the type of aggregation each provides.

Count Distinct Versus Count

One of the aggregate functions you can use in Tableau is Count (Distinct), which returns the number of unique values in a field.






These functions count records in different ways. Consider a data set that includes 10,000 records with 20 different regions. Performing a Count Distinct on the Region field returns a value of 20. The purpose of Count Distinct is to count the unique instances of a particular item. A Count aggregation of 10,000 records will result with an answer of 10,000 only because it counts all records.

Login or Signup to post a comment

Didn't find what you were looking for?

Start a new topic