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.

Solution:

Using the new Window Function in Pyramid 2023

1. Click on Edit Context Calculation in the top of your menu ribbon 
2. Select Measure > % Total Sales
3. Select Compute Used - Specific Chip
4. Select Specific Chip > Product Category. This will restrict the sub-total to the Product Category attribute.
5. 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.