List of Predefined Aggregations in Tableau?

[vatsallya-lamgadey-926c32c9 (Vatsallya Lamgadey)]

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. For example, a measure that contains the values 1, 2, 3, 3, 4 aggregated as a sum returns a single value: 13. Or 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 have the highest revenue.

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

Note: Using floating-point values in combination with aggregations can sometimes lead to unexpected results. For details, see Understanding data types in calculations(Link opens in a new window).

Tableau provides a set of predefined aggregations that are shown in the table below. You can set the default aggregation for any measure that is not a calculated field that itself contains an aggregation, such as AVG([Discount]). See Set the Default Aggregation for a Measure. You can also set the aggregation for a field already in the view. For details, see Change the Aggregation of a Measure in the View.

Aggregation	Description	Result for measure that contains 1, 2, 2, 3
Attribute	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. This aggregation is particularly useful when aggregating a dimension. To set a measure in the view to this aggregation, right-click (control-click on Mac) the measure and choose Attribute. The field then changes to show the text ATTR:

|N/A|
|Dimension|Returns all unique values in a measure or dimension.|3 values (1, 2, 3)|
|Sum|Returns the sum of the numbers in a measure. Null values are ignored.|1 value (8)|
|Average|Returns the arithmetic mean of the numbers in a measure. Null values are ignored.|1 value (4)|
|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.

This aggregation is not available for the following types of workbooks:

• Workbooks created before Tableau Desktop 8.2 and that use Microsoft Excel or Text File data sources.
• Workbooks that use legacy connections.
• Workbooks that use Microsoft Access data sources.

If you are connected to a workbook that uses of one of these types, Count (Distinct) is unavailable and Tableau shows the message “Requires extract.” To use this aggregation, extract your data. See Extract Your Data.|1 value (3)|
|Minimum|Returns the smallest number in a measure or continuous dimension. Null values are ignored.|1 value (1)|
|Maximum|Returns the largest number in a measure or 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 (3)|
|Std. Dev (Pop.)|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.|1 value (0.7071)|
|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 represents a sample of the population.|1 value (0.6667)|
|Variance (Pop.)|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.|1 value (0.5000)|
|Disaggregate|Returns all records in the underlying data source. To disaggregate all measures in the view, select Aggregate Measures from the Analysis menu (to clear the check mark).

Tableau allows you to view data in disaggregated form (relational databases only). 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 (see How to Disaggregate Data). Another way to look at disaggregated data is to view the underlying data for all or part of a view. For more details, see View Underlying Data.|4 values (1, 2, 2, 3)|

You can also define custom aggregations as described in Aggregate Functions in Tableau(Link opens in a new window). 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).

Set the Default Aggregation for a Measure

You can set the default aggregation for any measure that is not a calculated field that itself contains an aggregation, such as AVG([Discount]). A default aggregation is a preferred calculation for summarizing a continuous or discrete field. The default aggregation is automatically used when you drag a measure to a view.

To change the default aggregation:

Right-click (control-click on Mac) a measure in the Data pane and select Default Properties > Aggregation, and then select one of the aggregation options.

Note: You can use Tableau to aggregate measures only with relational data sources. Multidimensional data sources contain aggregated data only.

You cannot set default aggregations for published data sources. The default aggregation is set when the data source is initially published. Create a Local Copy(Link opens in a new window) of the published data source to adjust the default aggregation.