Aggregating Data

When you place a measure on a shelf, Tableau automatically aggregates the data, usually by summing it. You can easily determine the aggregation applied to a field because the function always appears in front of the field’s name when it is placed on a shelf. For example, Sales becomes SUM(Sales).

Note:

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

You can change the aggregation of a field by selecting a different function from the field’s context menu. As shown below, all of the predefined aggregations are available from this menu.


A graphic depicting how to change the aggregation of a measure using the field’s context menu.

Aggregating Measures

You can assign a different aggregation to every measure you place on a shelf. For example, you can aggregate Sales as a summation, Profit as a maximum, and Discount as an average.

You can change the aggregation state for all the measures on a worksheet by selecting the Analysis > Aggregate Measures menu item.


A graphic depicting the Aggregate Measures command on the Analysis menu.

When all measures are disaggregated you see a mark for each row in the view. You cannot select specific marks to Keep Only, Exclude, or create a Set when all measures are disaggregated.

Aggregating Dimensions

Dimensions can be aggregated as a measure using Minimum, Maximum, and Count. When you aggregate dimensions, you create a new temporary measure column, so the dimension is now viewed as a measure.


A graphic depicting how to aggregate a dimension using the options in the field’s context menu.

Another way to view a dimension is to treat it as an Attribute. When you aggregate a dimension as an attribute, it is treated like a label instead of partitioning the data. The Attribute aggregation has several uses such as ensuring a consistent level of detail when blending multiple data sources, providing a way to aggregate dimensions when computing table calculations, which require an aggregate expression, and finally it can increase query performance because it is computed locally.

Tableau computes Attribute with a the following formula:

IF MIN([dimension]) = MAX([dimension]) THEN MIN([dimension]) ELSE “*” END

The above formula is computed in Tableau after the data is retrieved from the initial query. The asterisk (*) is actually a visual indicator of a special type of Null value that occurs when there are multiple values. Refer to Troubleshooting Data Blending to learn more about the asterisk.

Below is an example of using Attribute in a table calculation. The table shows sales by market, market size, and state. Now suppose you wanted to compute the percent of total sales each state contributed to the market. When you add a Percent of Total Quick Table Calculations that computes along State, the calculation computes within the red area shown below. This is because the Market Size dimension is partitioning the data.



When you aggregate Market Size as an Attribute, the calculation is computed within the Market and the Market Size information is used purely as a label in the display.