Published: 18 Oct 2017 Last Modified Date: 12 Apr 2023
Question
How to segment grand totals to create a more detailed summary.
For example, if the highlight table shows a column for every year's sales within each category, we may want to segment grand total by year so that there is a total of sales across all categories for each year.
Environment
Tableau Desktop
Answer
Option 1: Use a viz in tooltip to show each year's total in relation to all years
The example workbook "Segmented Grand Totals.twbx" downloadable from the right-hand pane of this article, uses the sample data set Superstore. The workbook demonstrates how to add a visualization in a tooltip to allow the end user to see the trend across years in the tooltip. For more information on visualizations in a tooltip, see Viz in tooltip.
These directions start from the "Original" worksheet. The workbook contains detailed directions for how to create the "Original" worksheet.
Create a worksheet to show the desired totals. In this example, that is the "sales per year" worksheet, navigate to the "sales per year" worksheet to see detailed directions on how it was created.
Click Tooltip on the Marks card.
In the Edit Tooltip dialog, click Insert > Sheets > sales per year.
Option 2: Allow the user to hide Category to see Year totals
The example workbook "Segmented Grand Totals.twbx" downloadable from the right-hand pane of this article, uses the sample data set Superstore to create a parameter to either hide or show the Category.
These directions start from the "Original" worksheet. The workbook contains detailed directions for how to create the "Original" worksheet.
Click the down arrow next to Dimensions in the data pane and select Create Parameter…
In the Create Parameter dialog, do the following and click OK:
Name the parameter. In this example I will call it "Toggle Category"
For Data Type, choose Boolean
Optionally alias TRUE and FALSE
Right-click Toggle Category in the Data pane and select Show Parameter Control.
Select Analysis > Create Calculated Field
Name the calculated field. In this example, the calculated field is named "Category or blank"
In the formula field, create a calculation similar to the following: IF [Toggle Category] THEN [Category] ELSE '' END
Replace Category on the Columns shelf with Category or blank.
When the end user hides Category then they will see the year totals.
Option 3: Replace the year dimension with multiple calculated fields
The example workbook "Segmented Grand Totals.twbx" downloadable from the right-hand pane of this article uses the sample data set Superstore to create a separate calculated field to isolate each year's sales.
These directions start from the "Original" worksheet. The workbook contains detailed directions for how to create the "Original" worksheet.
Select Analysis > Create Calculated Field.
In the Calculated Field dialog box that opens, do the following, and then click OK:
Name the calculated field. In this example, the calculated field is named "2011 Sales"
In the formula field, create a calculation similar to the following: IF DATEPART('year', [Order Date]) = 2011 THEN [Sales] END
Repeat steps 1-2 for every year in the view.
Remove YEAR(Order Date) from the Columns shelf.
Remove Sales from both Label and Color.
Drag Measure Names to the Filters shelf.
In the Filters dialog, check only all of the calculations created in steps 1-3 and click OK.
Drag a second copy of Measure Names to the Columns shelf.
Drag Measure Vales to Label on the Marks card.
Drag a second copy of Measure Values to Color on the Marks card.
Additional Information
Thank you for providing your feedback on the effectiveness of the article.