Skip to content
For the complete documentation index, see llms.txt. This page is also available as Markdown.

Show Totals, Subtotals and Stacked Summaries

Add a grand total, subtotals per region and two measures in one cell to a Pivot Table, so a pipeline review shows both deal value and deal count.

The pipeline grid shows how much is open per region and stage. The sales manager now wants three more things on the same screen: a subtotal for each region split by business type, the total for the whole team, and the number of deals next to their value, so one large deal does not pass for a busy region.

Pivot Table grouped by region and business type with a subtotal per region, a grand total, and each cell showing an amount above a deal count
Subtotals per region, a grand total, and value with deal count in each cell.

Before you start

Steps

1

Turn on the grand total

Open the Component Builder on the Pivot Table. At the top of the Properties tab, turn on Show Grand Total.

The table gets a Total row at the bottom and a Total column on the right: the pipeline per stage across all regions, and per region across all stages.

2

Add a second row grouping

Subtotals summarize a group made of smaller groups, so the rows need a second level.

  1. In Data Mappings, under Group Rows, add an item below Region.

  2. Set Field to Type. Turn on Custom Label and enter Business as the Label.

The order matters: the first field is the outer group. Region first, then Type, gives one subtotal per region.

3

Turn on subtotals

Turn on Show Subtotals. Each region now gets a Subtotal row that adds up its New Business and Existing Business lines.

The columns keep a single grouping (Stage), so they get no subtotal column. To get one, add a second Group Columns field.

4

Count the deals beside their value

  1. Under Aggregations, add a second item.

  2. Set Field to Opportunity Name and Aggregation to Count: every opportunity has a name, so this counts the deals.

  3. Turn on Custom Label and enter Deals as the Label.

Each stage now has two columns side by side, headed Sum Pipeline and Count Deals: the header puts the aggregation before your label.

5

Stack the two values in one cell

Turn on Stacked Summaries. Each stage takes one column again, with the two values one above the other in each cell, and the measure names move into an extra column beside the row headers.

The Show Grand Total, Show Subtotals and Stacked Summaries toggles, all turned on
The three summary toggles, all on.
6

Save and test

Save the flow and run it.

Each region shows its two Business lines and a Subtotal row. The last row is the Total and the last column is the Total across stages. Every cell holds the pipeline amount with the deal count under it.

Troubleshooting

Show Subtotals is on, but no subtotal appears

The axis has only one grouping. Add a second field to Group Rows (or Group Columns) to get subtotals on that axis.

The subtotals group the wrong way

The first Group Rows field is the outer group. Reorder the items so the field you want subtotals for comes first.

Stacked Summaries changes nothing

It only has an effect with two or more Aggregations. With one measure, each cell already holds a single value.

Was this helpful?