Sigma Pivot Tables: Rows, Columns, Values, and Subtotals

If you've built pivot tables in Excel, Sigma's will feel familiar within a minute: rows down the side, columns across the top, values in the middle. The differences are in totals, in formulas that refer to totals, and in knowing when a grouped table does the job better.

Pivots are usually the first thing finance users rebuild when they move a report to Sigma. If you want help moving a set of Excel or Tableau reports, that's part of our Sigma Computing consulting work.

Key takeaways

• A Sigma pivot table has three properties: Pivot rows, Pivot columns and Values. It shows nothing until you add a row or a column.
• Numeric values are summed by default, and text and date values are counted. Change it from the column's Set aggregate menu.
• Totals are on by default. Hide grand totals or subtotals under Format > Totals, and change a subtotal's aggregate or write a custom formula for it.
• PercentOfTotal and Subtotal take pivot modes such as "row", "column" and "row_parent", for percent of row, column, or parent subtotal.
• Use a grouped table instead when you only need one direction of grouping, want to expand into row detail, or need running totals within groups.

How do you create a pivot table in Sigma?

Add a pivot table from Add element > Data > Pivot table, or as a child of an existing table, then fill in the three properties in the editor panel.

1. Select the pivot table and open the Properties tab.
2. Under Pivot rows, add Region.
3. Under Pivot columns, add Quarter of Order Date.
4. Under Values, add Revenue. Sigma sums it.

Here is the result on synthetic data, in thousands of dollars:

Change the aggregate

Sigma sums numbers and counts text and dates by default. To change it, hover the value in the editor panel, click the caret, and pick from Set aggregate: Sum, Avg, Median, Min, Max, Count, CountDistinct and others, per the pivot table docs.

Add a second row level

Add Product Type under Region in Pivot rows and the rows nest. By default they show in one stepped column. Click Display as separate columns to give each level its own column. Click + or - next to a row to expand or collapse it.

Sigma's docs advise doing complex transformations in the flattened source table rather than in the pivot. Clean names and calculated fields belong upstream.

How do subtotals and grand totals work in a Sigma pivot table?

Grand totals appear whenever the pivot has values. Subtotals appear when there's more than one pivot row or pivot column. By default each total uses the same formula as the value, applied one level up, per Sigma's totals and subtotals page.

Two limits to know. If you rename a total, child elements and formulas still refer to the original name, such as Revenue (Grand Total). And if two crossing totals use different aggregates, such as an average and a max, the cells where they meet can be blank.

How do you show percent of total in a pivot table?

Add a calculated column in Values with + > Add new column, and use PercentOfTotal with a pivot mode.

PercentOfTotal(Sum([Revenue]), "row")

That gives each quarter's share of the region's total. In the synthetic data, West's Q3 is 150 of 405, or 37.0%. Other modes:

The aggregate must be a full formula, such as Sum([Revenue]), not a reference to an existing value column. Subtotal takes the same modes and returns the total itself, so Sum([Revenue]) / Subtotal(Sum([Revenue]), "row") matches the "row" example.

For a one-off calculation inside the pivot without changing the source table, Sigma also has ad hoc calculations.

Which formatting options matter for reports?

Four settings cover most report layouts. They're under the pivot's Format tab.

• Repeat row labels. With rows shown as separate columns, prints the parent label on every row, which is what people usually want in an Excel export.
• Empty cell display value. Fills blank cells with a value you choose, such as 0 or a dash.
• Swap rows with columns. The button next to Pivot rows flips the layout.
• Values position. With two or more values, drag the Values box above or below the pivot columns or rows to change how they nest.

You can also hide the fixed row and column headers under Table components. Sigma notes that exports with hidden headers keep that layout only in PDF and PNG.

When should you use a grouped table instead of a pivot table?

Use a grouped table when the data only groups in one direction, or when you need things a pivot doesn't do well. Sigma's docs say grouping columns in a table can approximate some pivot functionality, such as aggregating data for specific groups.

In practice, a grouped table is the better choice for a list report with a header per group, and a pivot is the better choice for a matrix. Our post on merging cells in Sigma shows the same data both ways, and covers repeating row labels for export.

Check the flattened data

Maximize a pivot table (or press the space bar with it selected) to see the underlying flattened table. When a total looks wrong, that's where to look. Changes there flow into the pivot, and the other way round.

Pivot tables from Excel, Tableau and Power BI

Most pivot logic moves across cleanly. The usual work is in calculated fields and totals.

• Excel calculated fields become calculated columns in the source table or in Values. The Excel to Sigma functions cheat sheet covers the formula changes.
• Show values as % of row becomes PercentOfTotal with "row".
• Tableau table calculations usually map to PercentOfTotal, Subtotal, or window functions. See the Sigma functions list for the full set.
• Power BI matrix visuals map to pivot tables, but measures need their filter context rebuilt.

If a migration involves dozens of matrix reports, see our Tableau to Sigma and Power BI to Sigma services, and how to validate a Sigma migration for checking totals against the old reports.

Frequently asked questions

What to do next

Rebuild one Excel pivot you use every month in Sigma, then compare its totals to the spreadsheet before you retire the file. If you have a set of pivot-heavy reports to move, book a call with me.