See all tips | View as pdf

Aggregating and Disaggregating Data

It is common to view numerical data in an aggregated form such as a summation or an average. For example, if you have data for 3,000 sales transactions for 50 products, you might want to sum up all the sales figures for each product so that you can identify your top selling products. However, it is sometimes useful to disaggregate your data so that you can view every row of the data individually. After you identify your top selling products, you can then disaggregate the data to see the distribution of individual sales transactions.

Tableau makes it easy to aggregate and disaggregate data for specific fields or globally across all measures in the view.

Here's how it works.

The view below shows several customers and the total amount of money they have spent at the superstore. The customers are sorted in order of total sales with the highest sales first. As you can see, the Sales Total measure is aggregated as a SUM.
A view showing the total sales by customer.
You can change the aggregation using the field menu. Right-click the Sales Total measure on the Rows shelf and select Average.
Right-click the Sales Total measure on the Rows shelf and select Average.
Now the view shows the average amount each customer has spent at the superstore.
Right-click the Sales Total measure on the Rows shelf and select Average.
When you look at the aggregated data in the views above, each bar represents all transactions for a specific customer summed up or averaged into a single value. Now say that you want to see the individual transactions for each customer. You can create a view like that by selecting Analysis > Aggregate Measures.
Select Analysis > Aggregate Measures to disaggregate the data.
The menu item is then deselected and the Sales Total measure is disaggregated. The view now shows a mark for every row in the data source.
A view showing every transaction in the data source by customer.
You can see that all of the customers have had multiple transactions some of them nearing $30,000. You can add another dimension by dragging the Product Category 1 field from the schema to the Color shelf. The view below shows the product distribution by customer. You can now see that in general, customers tend to spend more on technology than they do on office products and furniture.
A view showing every sales transaction in the data source color encoded to show product category.
Note: The Aggregate Measures command on the Analysis menu controls the aggregation for all measure in the view. You can also disaggregate measures individually using the field menu. Right-click the measure you want to disaggregate and select All Values.
 

To Learn More

  • Learn more about Aggregated and Disaggregated Data by searching for Aggregations in the Tableau Online Help.
  • Contact Tableau Technical Support at: support@tableausoftware.com
  • Learn this trick and many more at the Tips and Tricks index. Don't forget to bookmark it.