How to add subtotals for % Values to sum up to 100% in the category
Question: How to add subtotals for % to sum up to 100% for the category
In a recent internal discussion we came across this question and wanted to share the solution to a broader audience. So, if you want to do subtotals and one of my fields is a %, but you would like the sub totals to add up to 100% and not show a percentage of the total as shown below, how do I accomplish that.
Ideally we would like to do this directly in discover.
Using the new Window Function in Pyramid 2023
- Click on Edit Context Calculation in the top of your menu ribbon
- Select Measure > % Total Sales
- Select Compute Used - Specific Chip
- Select Specific Chip > Product Category. This will restrict the sub-total to the Product Category attribute.
- Make sure the chip you choose to restrict the % to is in the right place in the report. If not it might seem it did not work, the sort order of the report needs to be correct
Building the same calculation in Formulate
- Open a new Formulate, we will then build the same logic for the defined context calc where all the Product Categories are displayed. If you remove a category, the context calc will show the % of the visual subtotal, whereas the Formulate calc will remain unchanged in its values
Slight caveat, the formulate will show the subtotal as a % of all the categories. Which in some circumstances might what you want
Let us know your thoughts about this solution below this Article.
Very useful function!
- 5 Likes
- 3 mths agoLast active
- 2 Following