November 8, 2022

An Easier Way to Calculate Percent of Total

Erica Chase
Product Manager
An Easier Way to Calculate Percent of Total

Numbers without context can be hard to interpret. That’s why looking at a number as a percent of a whole is such a powerful tool. It lets you contextualize the performance of an individual data point within a group. That’s why Sigma has made it easier than ever to create percent of total calculations. 

Adding a percent of total calculation to any workbook data element is now possible on visualizations, pivot tables and grouping tables. You will now see a new “Advanced Calculations” section of the ‘Set aggregate’ menu.

You can also use the PercentOfTotal function directly in the function bar to set up your own percent of total calculations. We’ve added two more functions – Subtotal and GrandTotal – which allow you to reference totals and subtotals at different levels of granularity. All three of these new functions are usable like any other aggregate calculation in Sigma.

Dynamically Calculate Percent of Total as You Drill Down

One powerful capability of the PercentOfTotal function is that it dynamically references calculations in a data element. Here’s an example: Let’s say you set up a percent of total function that references the x-axis of your chart. If a drill down changes the calculation on the x-axis, such as drilling down from year to month, the percent of total calculation will dynamically recalculate. These new functions create a powerful pairing with our drill anywhere functionality, helping users drill in and get contextual data on how the business is performing.

Let’s take a look at what that can look like in practice. 

First, we set up a chart that shows sales performance per year, broken down by region. We add a percent of total by x-axis calculation to the tooltip, so users can easily see what percentage of sales each region was responsible for in the respective years. 

A user might see that sales in the Midwest seem to be decreasing year over year, and want to drill in to see more granular data. They click on the 2022 bar, and drill by month.

The percent of total calculation in the tooltip automatically updates to reference the updated x-axis and shows percent of total by month. The calculation adapts to the new context, giving users the information they expect to see. 

The new functionality also makes it easy to set up percent of total calculations in pivot tables and grouping tables. In a few clicks, set up percent of total by row, column, or grand total in your pivot tables.

Quickly calculate percent of totals in your grouping tables without having to set up aggregate calculations in every group. By default PercentOfTotal references the closest parent grouping. This can be overridden to reference a higher parent grouping by adding a parameter to the formula.

At Sigma, we always strive to make the easy things easier, and the hard things possible. Try out our new percent of total functionality, then celebrate your life getting just a little bit easier.

We are Sigma.

Sigma is a cloud analytics platform that uses a familiar spreadsheet interface to give business users instant access to explore and get insights from their cloud data warehouse. It requires no code or special training to explore billions or rows, augment with new data, or perform “what if” analysis on all data in realtime.