What do you understand by aggregation and disaggregation of data in Tableau?

Data values in Tableau are broadly treated in two ways; aggregation or disaggregation . In aggregation, data values (particularly measure values) are combined together or aggregated to give a summarized or higher level of data for analysis. For instance, if we have a measure field containing sales values for different cities. We can aggregate these values to create averages, sums, etc. Aggregated fields are very useful in analysis.

On the other hand, when we disintegrate grouped or aggregated data into its basic form then it is called disaggregation of data values.

For instance, if in an analysis we need to see which age group has the most frequent buyers of a product, we need disaggregated values of data because it makes the data values distinct and discrete instead of clubbing them together.

Tableau aggregates data in your view by default. … When you disaggregate measures, you no longer are looking at the average or sum for the values in the rows in the data source. Instead, the view shows a mark for every row in the data source. Disaggregating data is a way to look at the entire surface area of the data.

When you add a measure to the view, Tableau automatically aggregates its values. Sum, average, and median are common aggregations; for a complete list, see List of Predefined Aggregations in Tableau.

The current aggregation appears as part of the measure’s name in the view. For example, Sales becomes SUM(Sales). Every measure has a default aggregation which is set by Tableau when you connect to a data source. You can view or change the default aggregation for a measure—see Set the Default Aggregation for a Measure.

You can change the aggregation for a measure in the view from its context menu:

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

Aggregating Dimensions

You can aggregate a dimension in the view as Minimum, Maximum, Count, or Count (Distinct). When you aggregate a dimension, you create a new temporary measure column, so the dimension actually takes on the characteristics of a measure.

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

Note: The Count (Distinct) aggregation is not supported for Microsoft Access data sources, and for Microsoft Excel and Text File data sources using the legacy connection. If you are connected to one of these types of data sources, the Count (Distinct) aggregation is unavailable and shows the remark “Requires extract.” If you save the data source as an extract, you will be able to use the Count (Distinct) aggregation.

Another way to view a dimension is to treat it as an Attribute. Do this by choosing Attribute from the context menu for the dimension. The Attribute aggregation has several uses:

  • It can ensure a consistent level of detail when blending multiple data sources.
  • It can provide a way to aggregate dimensions when computing table calculations, which require an aggregate expression.
  • It can improve query performance because it is computed locally.

Tableau computes Attribute using the following formula:

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

The 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. See Troubleshoot Data Blending(Link opens in a new window) 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. 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 calc (see Quick Table Calculations(Link opens in a new window)) 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 (East, in the following image), and the Market Size information is used purely as a label in the display.