Was this page helpful?
Yes No
Have a comment? Please leave it here.Thanks for your feedback!
All Tableau Help > Tableau Desktop and Web Authoring Help > 

Data Aggregation in Tableau

In Tableau, you can aggregate measures or dimensions, though it is more common to aggregate measures. Whenever you add a measure to your view, an aggregation is applied to that measure by default. The type of aggregation applied varies depending on the context of the view.

Watch a Video: To see related concepts demonstrated in Tableau, watch Aggregation, Granularity, and Ratio Calculations, a 4-minute free training video. Use your tableau.com account to sign in.

In this article

List of Predefined Aggregations in Tableau

Set the Default Aggregation for a Measure

Change the Aggregation of a Measure in the View

Aggregating Dimensions

How to Disaggregate Data

Example: Scatter Plots, Aggregation, and Granularity

Change the Aggregation of a Measure in the View

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 aggregate measures using Tableau only for relational data sources. Multidimensional data sources contain data that is already aggregated. In Tableau, multidimensional data sources are supported only in Windows.

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.

Back to top


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:

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 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) 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.

Back to top


List of Predefined Aggregations in Tableau

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.

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.

AggregationDescriptionResult 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
DimensionReturns all unique values in a measure or dimension.3 values (1, 2, 3)
SumReturns the sum of the numbers in a measure. Null values are ignored.1 value (8)
AverageReturns the arithmetic mean of the numbers in a measure. Null values are ignored.1 value (2)
Median

Returns the median of the numbers in a measure. Null values are ignored.

This aggregation is not available for workbooks created before Tableau Desktop 8.2 or that use legacy connections. It is also not available for connections using any of the following data sources:

  • Access

  • Amazon Redshift

  • Cloudera Hadoop

  • HP Vertica

  • IBM DB2

  • IBM PDA (Netezza)

  • Microsoft SQL Server

  • MySQL

  • Teradata

If you are connected to a workbook of that uses one of these data sources, Median is unavailable and shows the message "Requires extract." To use this aggregation, extract your data. For more details, see Extract Your Data.

1 value (2)
CountReturns the number of rows in a measure or a 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.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)
MinimumReturns the smallest number in a measure or continuous dimension. Null values are ignored.1 value (1)
MaximumReturns the largest number in a measure or a continuous dimension. Null values are ignored.1 value (3)
Percentile

Returns the value at the specified percentile for the measure. When you select this aggregation, you must choose from a submenu offering a range of percentile values: 5, 10, 25, 50, 75, 90, 95. When you set this aggregation on a field in the view, the field shows PCT and the percent value assigned. For example:

If you want to use a percentage value other than the ones listed, use the PERCENTILE function in a calculation and specify the percentile you want. For more details, see Aggregate Functions in Tableau.

This aggregation is available for:

  • Non-legacy Microsoft Excel and Text File connections.

  • Extracts and extract-only data source types (for example, Google Analytics, OData, or Salesforce).

  • Sybase IQ 15.1 and later data sources.

  • Oracle 10 and later data sources.

  • Cloudera Hive and Hortonworks Hadoop Hive data sources.

  • EXASolution 4.2 and later data sources.

If you are not connected to a workbook that uses one of these data sources, Percentile is unavailable and Tableau shows the message "Requires extract." In this case, if you want to use the Percentile aggregation, consider creating an extract. See Extract Your Data.

1 value. The value for PCT50 would be 2 for the given data.
Std. DevReturns the standard deviation of all values 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 (0.8165)
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)
VarianceReturns 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. 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).

Back to top


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 of the published data source to adjust the default aggregation.

Back to top


How to Disaggregate Data

Whenever you add a measure to your view, an aggregation is applied to that measure by default. This default is controlled by the Aggregate Measures setting in the Analysis menu.

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

To disaggregate all measures in the view:

When Aggregate Measures is selected, Tableau will attempt to aggregate measures in the view by default. This means that it collects individual row values from your data source into a single value (which becomes a single mark) adjusted to the level of detail in your view.

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

For a complete list of the available aggregations, List of Predefined Aggregations in Tableau.

The level of detail is determined by the dimensions in your view—for information about the concept of level of detail, see How dimensions affect the level of detail in the view.

Disaggregating your data can be useful for analyzing measures that you may want to use both independently and dependently in the view. For example, you may be analyzing the results from a product satisfaction survey with the Age of participants along one axis. You can aggregate the Age field to determine the average age of participants or disaggregate the data to determine at what age participants were most satisfied with the product.

Disaggregating data can be useful when you are viewing data as a scatter plot. See Example: Scatter Plots, Aggregation, and Granularity.

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

 

Back to top


Example: Scatter Plots, Aggregation, and Granularity

If you place one measure on the Rows shelf and another measure on the Columns shelf, you are asking Tableau to compare two numerical values. Typically, Tableau chooses a scatter plot as the default visualization in such cases. The initial view will most likely be single mark, showing the sum for all values for the two measures. This is because you need to increase the level of detail in the view.

Start building the scatter plot

Use dimensions to add detail

Try adding more fields to the rows and columns shelves

Try disaggregating the data

Start building the scatter plot

There are various ways to add detail to a basic scatter plot: you can use dimensions to add detail, you can add additional measures and/or dimensions to the Rows and Columns shelves to create multiple one-mark scatter plots in the view, or you can disaggregate the data. And, you can also use any combination of these options. This topic looks at these alternatives using the Sample-Superstore data source.

To create the initial view, follow these steps:

  1. Place the Sales measure on the Columns shelf.

  2. Place the Profit measure on the Rows shelf.

The measures are automatically aggregated as sums. The default aggregation (SUM) is indicated in the field names. The values shown in the tooltip show the sum of sales and profit values across every row in the data source.

Follow the steps below to use dimensions to add detail to the view and to disaggregate data.

Back to top


Use dimensions to add detail

Follow these steps to develop the scatter plot view you created above by adding dimensions to show additional levels of detail.

  1. Drag the Category dimension to Color on the Marks card.

    This separates the data into three marks—one for each dimension member—and encodes the marks using color.

  2. Drag the State dimension to Detail on the Marks card.

    Now there are many more marks in the view. The number of marks is equal to the number of distinct states in the data source multiplied by the number of categories.

Although more marks are now displayed, the measures are still aggregated. So regardless of whether there is one row in the data source where State = North Dakota and Category= Furniture, or 100 such rows, the result is always a single mark.

Maybe this process is developing the view in a direction you find useful, or maybe you prefer to go in a different direction—for example, by adding a time dimension to the view, or by introducing trend lines or forecasting. You decide what questions to ask.

Back to top


Try adding more fields to the rows and columns shelves

Revert to the original one-mark view and follow these steps to develop the scatter plot view by adding fields to the Rows and Columns shelves.

  1. Drag the State dimension to the Columns shelf.

    Even if you drop Continent to the right of SUM(Sales), Tableau moves it to the left of SUM(Sales). This is because you cannot insert a dimension within a continuous axis. Instead, your view shows a separate axis for each member of the dimension.

  2. Drag the Segment dimension to the Rows shelf.

    You now have a view that provides an overview of Sales and Profit across states and customer segments. It can be interesting to hover over the marks in the view to see tooltip data for various segments:

    Back to top


Try disaggregating the data

Another way to modify your original one-mark scatter plot to display more marks is by disaggregating the data.

Clear the Analysis >Aggregate Measures option. If it is already selected, click Aggregate Measures once to deselect it.

What you have actual done is to dis-aggregate the data, because this command is a toggle that was originally selected (check mark present). Tableau aggregates data in your view by default.

Now you see a lot of marks—one for each row in your original data source:

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. It's a quick way to understand the shape of your data and to identify outliers. In this case, the disaggregated data shows that for many rows in the data, there is a consistent relationship between sales income and profit—this is indicated by the line of marks aligned at a forty-five degree angle.

 

 

Back to top