Data Aggregation in Tableau

It is useful to look at numeric values using different aggregations function. Tableau supports many different aggregation types, such as:

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

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

If you are not familiar with the database, then refer to Tableau manual for detailed definition of these aggregate types. You are adding fields into the visualization by default then it will be displayed.

Tableau 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 its default by selecting the menu options (default properties or Aggregation).

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

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

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

Aggregating Measures

When you add a measure to the view, Tableau automatically aggregates its value. Average, sum and median are the common aggregation functions. The current Aggregation looks like part of the measure's name in the view.

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

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

Set the default Aggregation for Measures

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

To change the default Aggregation

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

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

How to Disaggregate the Data

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

If you want to see all of the marks in the view at the most detailed level of the model, you can disaggregate the view. Disaggregating your data means 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 aggregation measures option. When Aggregate Measures is selected, then automatically Tableau will attempt to aggregate measures in the view. Means that it collects individual row values from your data source into a single value that is adjusted to the level of detail in your view.

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

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

Disaggregating data can be useful for analyzing measures which you want to use both dependently and independently in the view.

Note: If your data source is very large, then, as a result, disaggregating the data can degrade in significant performance.

Aggregating Dimensions

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

Note: The Count Distinct aggregation does not support the Text File and Microsoft Excel data sources using the inheritance connection. If you are connected to one of these types of data sources, then the Count Distinct aggregation is unavailable, and it shows the remark "Requires extract." If you save the data sources as an extract, you will be able to use the Count Distinct aggregation.

Another way to view a dimension as an attribute. You can change it by choosing the Attribute from the context menu for the dimension.

The attribute aggregation has several uses:

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

Tableau calculates the Attribute using the below given formula:

• This given formula is calculated in Tableau after the data is retrieved from the initial query.
• The asterisk (*) is 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 percent of the total sales according to each state contribution for the market. When you add some Percent of Total in table calculation that calculates along 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.