A common question in Sigma is how to best create percent of total calculations. That’s why Sigma recently released three new functions that make it much easier to create percent of total calculations.
PercentOfTotal - Returns the percent of total for an aggregate formula.
Subtotal - Returns the subtotal for an aggregate formula.
GrandTotal - Returns the grand total for the given aggregate formula. This function is shorthand for Subtotal(aggregate, “grand_total”)
There is also a new ‘Advanced Calculations’ section of our ‘Set Aggregate’ drop down menu, which helps guide you through using these functions in the most common cases.
Percent of Total by Category
Using the new PercentOfTotal function, it is straightforward to calculate the percent of total by category. In this example, I will show the percentage of total flights by airline and day of week.
First, set up your graph with the absolute calculations. In this case, we’ll put [Day of Week] on the x-axis, [Count of Flight Number] on the y-axis, and [Airline] on the color field. This will show us the absolute number of flights by airline and day of week.
To change this to showing the percent of total by airline, we simply need to right click on [Count of Flight Number] and go to ‘Set Aggregate’ then ‘Show Percent of Total’ and choose ‘By Color’.
This will update your aggregate to show the percentage of flights per day of week when looking at the total flights for each airline.
Percent of Total by Grand Total
To set up percent of total by grand total is just as easy. Once again, set up your chart with the absolute number. Then, open up the set aggregation menu, and choose ‘Show Percent of Total’ then ‘By Grand Total’.
This process works for any chart type.
Percent of Total in Pivot Tables
The new PercentofTotal function also works in pivot tables, making it easy to calculate percent of total by row, column, or by grand total. Simply set up your aggregation with the absolute numbers, then use the aggregation menu to choose the percent of total calculator to add.
If you have multiple parent levels in your pivot table, you can optionally specify which level of row parent you would like to reference in your percent of total calculation. By default, we reference the closest parent. You can specify if you want to reference a parent higher in the grouping.
In the example below, by default we would reference the closest parent “Month”. By specifying “2” we can reference the next level parent, which is “Day of Week”.
