Tableau, by default, aggregates measure values, and this behavior can be changed to show all individual values of the measures by clicking on the Analysis menu option from the top toolbar and unchecking Aggregate Measures to remove aggregation. It is also possible to change the aggregation type, such as total, average, variance, and others.
Let's use the sample file Sample – Coffee Chain (Access). Open a new worksheet and select Sample – Coffee Chain (Access) as the data source.
Once the data is loaded, perform the following steps to change and add various aggregate measures:
- Make sure the Aggregate Measures option is checked under the Analysis menu option on the top toolbar.
- Drag-and-drop Product Type from Dimensions into the Rows shelf.
- Drag-and-drop Profit from Measures into the Text input box under Marks.
- To view the average profit by Product Type, click on SUM(Profit) in the Text input box, expand Measure (Sum), and select Average, as shown in the following screenshot:
- Click on the Show Me button to bring the Show Me toolbar on the screen.
- To add total profit by Product Type, click on Profit from Measures and click on the text tables icon on the Show Me toolbar, as shown in the following screenshot:
- To add the maximum profit value by Product Type, click on SUM(Profit) under Measure Values, expand Measure (Sum), and select Maximum.
- To add the total profit by Product Type again, drag-and-drop Profit from Measures into the Measure Values pane. Once all the aggregate values are added, the table should look like the one in the following screenshot:
Adding various aggregate measures of the same measure is somewhat counterintuitive and is hardly straightforward. Since Tableau allows one type of aggregation only once, users must change the aggregation type of the already displayed aggregation and add the measure again. You could also create duplicate copies of the measure and add the new measure with a different aggregation, but you will have to rename the measures.