Data Aggregation in Tableau

Aggregation is a calculation of some kind applied to the most granular level of data that helps to describe or summarize it in some way. Tableau supports many aggregation types, such as:

• Sum
• Average
• Count
• Count Distinct
• Median
• Minimum
• Maximum
• Variance
• Variance of Population
• Standard Deviation
• Standard Deviation of Population
• Attribute
• Dimension

You can create aggregation dimensions and measures in Tableau. When you add measures to your view, an aggregation is applied by default to those measures. The type of Aggregation depends on the context of the view.

Tableau also allows you to change or alter the aggregation level for a specific view. To change the default aggregation, do right-click on that field inside the data shelf and change it by selecting the menu options.

You can also alter the Aggregation of a field for specific use in the worksheet.

For example, selecting the menu option Measure (SUM) by right-clicking on the SUM (Sales) pill, you can choose any of the highlighted aggregations.

The data source used above is a data extract of an Excel spreadsheet. It is mandatory to understand that if you depend on the direct connection to Excel, the aggregations median and count (distinct) would not be available as Access, Excel, and text files do not support these aggregate types. Tableau’s extract engine does this task.

Aggregating Measures:

Whenever you add a measure to the view, Tableau automatically aggregates its value. Average, sum, and median are the common aggregate functions. The current Aggregation looks like it is a part of the measure’s name in the view.

For example, the Sales becomes SUM (Sales), and every measure has a default aggregation set by Tableau whenever you connect to a data source. You can alter or view the default aggregation for measures.

• You can also aggregate a measure using Tableau only for relational data sources.
• Multidimensional data sources contain data sources that are already aggregated.
• In Tableau, the multidimensional data source is only supported in windows.

Set the default Aggregation for Measures:

You can also set the default aggregations for any measures. It is not a calculated field containing an aggregate, like AVG ([Discount]). A default aggregation is actually a preferred calculation for summarizing a discrete or continuous field and is used when you drag a measure automatically to a view.

To change the default Aggregation:

Right-click on the measure menu option in the Data field and select Default Properties, select Aggregation and select one of the aggregation options.

You cannot set default aggregation for a published data source. The default aggregation is only set when the data source is initially published.

How to Disaggregate the Data?

When you add the measure to your view, then Aggregation is applied to that measure automatically. It is controlled by the Aggregate Measures setting in the Analysis menu.

If you want to see all the marks in the view at the most detailed level of the model, you can disaggregate it. Disaggregating your data implies that the Tableau will display a separate mark for every data value in every row of your data source.

Disaggregation in all Measures in the view:

Click on the analysis then go to the aggregation measures option. When Aggregate Measures are selected, then automatically Tableau will attempt to aggregate measures in the view. This means that it collects the individual row values from your data source into a single value adjusted to the level of detail in your view.

The different aggregations that are available for measures determine how the individual values are gathered, they can be averaged (AVG), added (SUM), or can be set to the minimum (MIN) or maximum (MAX) value from the individual row values.

If it is already selected, click on the aggregation measures once for deselecting it. Then, you can see the changes.

Disaggregating data can help analyze measures that you want to use both dependently and independently in the view.

Aggregating Dimensions:

You can also aggregates dimension in the view as Maximum, Minimum, Count, and Count Distinct. Whenever you aggregate a dimension, you have to create a new temporary measure column, so that the dimension takes the characteristics of a measure.

Another method to view a dimension as an attribute is that you can change it by choosing the Attribute from the context menu.

The attribute aggregation has many uses:

• It ensures that a consistent level of detail should remain when blending multiple data sources.
• It also provides a way to aggregate the dimension when computing table calculations that require an aggregate expression.
• It also improves query performance due to locally computed.

Tableau calculates the Attribute using the below-given formula:

If MIN (dimension) = MAX (dimension) then MIN (dimension) else “*” end   

• The above formula is calculated in Tableau after the data is retrieved from the initial query.
• The asterisk (*) denotes a visual indicator of a special type of Null value it occurs when there are multiple values.

Above is an example of using Attribute in a table calculation. This table shows the market, market size, state, and sales by the market that is SUM (sales). Suppose you want to compute the percentage of the total sales according to each state contribution to the market. When you add some Percent of Total in table calculation that calculates along with State, the calculation computes within the black area shown above figure just because the Market Size of dimension is partitioning the data.

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