KNOWLEDGE BASE

Create Segmented Grand Totals


Published: 18 Oct 2017
Last Modified Date: 03 Sep 2019

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 to add a viz in tooltip to allow the end user to see the trend across years in the tooltip. Viz in tooltip was a new feature added in Tableau Desktop 10.5.

These directions start from the "Original" worksheet. The workbook contains detailed directions for how to create the "Original" worksheet.
  1. 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.
  2. Click Tooltip on the Marks card
  3. 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 toggle showing or hiding the dimension [Category].

These directions start from the "Original" worksheet. The workbook contains detailed directions for how to create the "Original" worksheet.
  1. Click the down arrow next to Dimensions in the data pane and select Create Parameter…
  2. In the Create Parameter dialog, do the following and click OK:
    1. Name the parameter. In this example I will call it "Toggle Category"
    2. For Data Type, choose Boolean
    3. Optionally alias TRUE and FALSE
  3. Right-click [Toggle Category] in the data pane and select Show Parameter Control
  4. Select Analysis > Create Calculated Field
    1. Name the calculated field. In this example, the calculated field is named "Category or blank"
    2. In the formula field, create a calculation similar to the following:

      IF [Toggle Category]
      THEN [Category]
      ELSE ''
      END
  5. Replace [Category] on the Columns shelf with [Category or blank]
  6. 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.
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "2011 Sales"
    2. In the formula field, create a calculation similar to the following:

      IF DATEPART('year', [Order Date]) = 2011
      THEN [Sales]
      END
  3. Repeat steps 1-2 for every year in the view
  4. Remove YEAR(Order Date) from the Columns shelf
  5. Remove [Sales] from both Label and Color
  6. Drag [Measure Names] to the Filters shelf
  7. In the Filters dialog, check only all of the calculations created in steps 1-3 and click OK
  8. Drag a second copy of [Measure Names] to the Columns shelf
  9. Drag [Measure Vales] to Label on the Marks card
  10. Drag a second copy of [Measure Values] to Color on the Marks card
Did this article resolve the issue?