Creating percent of totals in Sigma Computing is a fundamental and powerful analytical technique that enables data professionals and analysts to gain valuable insights into their data. Whether you’re working with financial data, sales figures, or any other dataset, calculating the percentage of a particular value relative to the total can help you understand patterns and trends and make better-informed decisions.
In this blog, you will understand how to leverage Sigma to calculate and visualize total percentages efficiently.
Why use Percent of Totals Calculations?
Percent of totals is a standard calculation used to analyze data by expressing individual data points or categories as a percentage of the overall total. It helps users understand the relative contribution of specific elements to the whole data.
For example, in Finance, it helps evaluate the contribution of financial metrics, guiding resource allocation and budgeting. Sales and Marketing teams benefit from it by pinpointing which products, regions, or marketing channels drive the most revenue, enabling strategic optimization. In the Retail sector, it informs product sales analysis and inventory management decisions, ensuring the right products are prioritized and stocked.
Percentages of total calculations should be used cautiously on sparse data, data with extreme outliers, or when comparing percentages across multiple subsets. For example, sparse data or data with extreme outliers can be swayed heavily by a single contributing factor. Percentages across differently sized data sources can incorrectly convey similar weight.
For example, 51% of voters in California are a larger number of people than 51% of voters in Montana. As with all analytics, it is important to understand the context and desired outcomes to choose the best analysis type.
How to Calculate the Percent of Totals in Sigma: Built-in Option
Step 1: Access Your Data
Before calculating the percentage of totals, you need to have your data connections set up in Sigma. Here, we will use the Sample Database source of PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA table and D_PRODUCT table joined together on SKU Number. We renamed the table as Product Analysis Table. Then, after the join, we created a new column, Revenue, in our data, which is a product of Quantity and Price.
Step 2: Create a Child Visualization
Once you’ve added a table element, create a new visual element where you want to perform the percent of totals calculation. We will use a vertical stacked bar chart showing yearly revenue by product type. Below are the steps to create a stacked bar chart:
Click on the Create a child element option in the table element (blue circle in the image below)
Select the Visualization option from the drop-down menu.
Drag the Date field to the x-axis, and right-click on the date field to truncate the date on the year.
Drag the Revenue field to the Y-axis.
In the Color Marks section, select “By category” in the drop-down menu and drag the Product Type field to it.
Step 3: Apply the Percent of Totals Calculation
Next, we will change the Revenue value displayed in the bar chart to be one of the percent of total options.
To calculate the percent of totals:
-
Click on the downward-facing arrow of the Sum of Revenue on the y-axis.
-
Hover over Set Aggregate and then the Advanced Calculations section, and then under Show Percent of Totals, you will find three options.
How to Calculate the Percent of Totals in Sigma: Percent of Total Option
By Grand Total – The “percentage of total by grand total” within the context of set aggregate functions calculates the proportion of each individual value in a set relative to the overall grand total of all values (see docs).
For example, in the image below, this 5.45% means Arts & Entertainment in 2022 represents 5.45% of all revenue from all product types in 2019-2023.
In other words, the formula would be
Percentage of Total by Grand Total = ( Revenue for Arts & Entertainment in 2020 / Total Revenue for all Product Types in 2019-2023 )
By Color – This option will appear if the visual is colored By category, as identified in the Color Marks section. Sigma will calculate the percentage of each individual value within its respective color category compared to the total within that color category.
For example, in the image below, 23.61% of all the Revenue earned in 2019-2023 for Arts & Entertainment was earned in 2020.
In other words, the formula would be
Percentage of Total by Color = ( Revenue for Arts & Entertainment in 2020 / Total Revenue for Arts & Entertainment in 2019-2023 )
By X-axis – This will compute the percentage of each data point in a visual as compared to the total along the X-axis.
For example, each year or bar adds up to 100% in the image below. So, of all the Revenue earned in 2020, 23.50% came from Arts & Entertainment.
In other words, the formula would be
Percentage of Total by Color = ( Revenue for Arts & Entertainment in 2020 / Total Revenue for all Product Types in 2020 )
Using the Formula Bar
Additionally, we can directly employ and edit the PercentOfTotal function in the formula bar, enabling us to create custom percent of total calculations tailored to our specific needs. These steps assume your data is already added to your workbook.
Below is the syntax of the Percent of Total function:
PercentOfTotal(aggregate, [mode], [parameters])
aggregate (required) – The aggregate formula to use, such as
Sum([Profits])mode (optional) – The mode to use when calculating the aggregate formula. This mode determines which dimensions are considered when the calculation is executed.
General purpose modes
"grand_total"(default for visualizations and pivot tables) – Computes the percent of grand total for the aggregate formula.
Visualization modes
"color"– Computes the aggregate percent of the total using only the column specified for COLOR."x_axis"– Computes the aggregate percent of the total using only the column(s) placed on the X-AXIS."trellis_column"/"trellis_row"– Computes the aggregate percent of the total using only the dimension specified for the TRELLIS COLUMN or TRELLIS ROW.
Pivot table modes
"column"/"row"– Computes aggregate percent of the total for the column or row total."column_parent"/"row_parent"– Computes the aggregate percent of the total for the column or row total relative to the subtotal calculation for the parent dimensions.
Table modes
"grouping_parent"(default for tables) – Computes the aggregate formula using the grouping keys from a parent grouping. This is equivalent to creating the aggregate calculation in a parent grouping and referencing it from a column in a lower grouping.
parameters (optional) – An additional parameter specific to the mode.
Exclusively relevant to the modes
"column_parent","row_parent", and"grouping_parent".This third parameter indicates the number of parent dimensions to be excluded during the calculation.
How to Calculate Percent of Totals in Grouped Tables
In the example below, we group sales data by [Store Region]. We then calculate the total sum of each row by creating a new calculation called [Sales].
Collapsing the table by grouping enables us to view this grouped sales and calculated data from a broader perspective.
Here we created a new calculation % of Total for Sum of Sales = PercentOfTotal(Sum([Sales]), "grand_total") within the table to compute the percentage sales contribution of each Store Region to the total sales.
For more information, please check the Tables in Sigma Computing.
How to Calculate Percentage of Subtotals in Pivot Tables
In the example below, we pivot sales data using Pivot rows as [Store Region] and [Store State] and Pivot columns as Year of Date which is filtered to 2023.
![A screenshot of a pivot table using Pivot rows as [Store Region] and (Store State] and Pivot columns as Year of Date, which is filtered to 2023.](https://i0.wp.com/www.phdata.io/wp-content/uploads/2024/01/image12.png)
We will calculate the % of total state sales broken down by region for the year 2023. For that, we will create a new calculation Percent of Total = [Sales] / [Sales (Parent Row Total)] in the Values section to compute the percentage of subtotals.
For more information, please check the Pivot Table Subtotals in Sigma Computing.
Closing
The percentage of totals is a foundational view for any analyst. In this blog, we demonstrated how Sigma makes it easy to create these calculations differently by providing built-in and customizable solutions. If you have any additional questions about calculating percent totals in Sigma, contact our team of experts. Happy analyzing!
FAQs
It calculates the aggregate percent of the total for the row total relative to the subtotal calculation for the parent dimensions.
In Sigma, using the Percentage of total calculations within visualizations, tables, and pivot tables can provide valuable insights into the relative contribution of different data elements to the overall total. Here's how you can effectively leverage Percent of Total in these contexts:
Visualizations:
Pie Charts and Donut Charts: Represent the percentage share of each category in a circular chart, making it easy to visualize the contribution of each part to the whole.
Stacked Bar Charts: Display the cumulative percentage contribution of different categories, allowing for quick comparisons.
Table:
Column Expressions: Create calculated columns using the Percent of Total function to display the percentage contribution of each row to the total within a table. This provides a clear breakdown of proportions.
Conditional Formatting: Apply conditional formatting to highlight rows or cells based on their percentage contribution. This makes it easier to identify significant contributors.
Pivot Table:
Row and Column Aggregation: Use the Percent of Total function in row and column aggregations to show the proportional contribution of each item to the total across different dimensions.
Filtering and Grouping: Apply filters or group data to focus on specific subsets, allowing for a more detailed analysis of percentages within a pivot table.
