Aggregations

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 result in a single value. For example, a measure that contains the values 1, 2, 3, 3, 4 aggregated as a sum results in a single value: 13.

Note:

You can aggregate measures using Tableau only for relational data sources. Multidimensional data sources contain aggregated data only.

For example, if you have 3,000 sales transactions from 50 products in your data source, you might want to view the sum of sales for each product, so that you can decide which products are the most important.

Tableau provides a set of predefined aggregations that are shown in the table below.
Aggregation Description Result for measure that contains 1, 2, 2, 3
ATTR Returns the value of the given expression if it only has a single value for all rows in the group, otherwise it displays an asterisk (*) character. Null values are ignored. N/A
Dimension Returns all unique values in a measure or dimension. 3 values (1, 2, 3)
Sum Computes the sum of the numbers in a measure. Null values are ignored. 1 value (8)
Average Computes the arithmetic mean of the numbers in a measure. Null values are ignored. 1 value (2)
Minimum Computes the smallest number in a measure or continuous dimension. Null values are ignored. 1 value (1)
Maximum Computes the largest number in a measure or a continuous dimension. Null values are ignored. 1 value (3)
Standard Deviation Computes 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. 1 value (0.8165)
Standard Deviation Population Computes 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. 1 value (0.7071)
Variance Computes 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 represents a sample of the population. 1 value (0.6667)
Variance Population Computes 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. 1 value (0.5000)
Count Counts 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. 1 value (4)
Count Distinct Counts 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. This function is not supported for Microsoft Access, Microsoft Excel, and Text file data sources. 1 value (3)
Disaggregate Returns all records in the underlying data source. 4 values (1, 2, 2, 3)

You can also define custom aggregations as described in Aggre gate Calculations. Note that depending on the type of data view you create, Tableau will apply these aggregations at the appropriate level of detail. For example, Tableau will apply the aggregation to individual dimension members (the average delivery time in the East region), all members in a given dimension (the average delivery time in the East, West, and Central regions), or groups of dimensions (the sum of sales for all regions and for all markets).

You may specify a default aggregation for any measure that is not a user-defined aggregation. A default aggregation is a preferred calculation for summarizing a continuous or discrete field. The default aggregation is automatically used when a measure is first placed on a shelf. Change the default aggregation by right-clicking a measure in the Data window and selecting Field Properties > Aggregation. Below the default aggregation for the Budget Margin measure is set to Average.


A graphic depicting the Average option on the Default Aggregation menu on the right-click context menu of a dimension in the schema.

Tableau also allows you to view data in disaggregated form (relational databases only). This is an extremely powerful feature. When data are disaggregated, you can view all of the individual rows of your data source. For example, after discovering that the sum of sales for rubber bands is $14,600, you might want to see the distribution of individual sales transactions. To answer this question, you need to create a view that shows individual rows of data. That is, you need to disaggregate the data (refer to How Agg regatio n and Disaggregation Work). Also, one way to look at disaggregated data is to view the underlying data that’s displayed in a table.