This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Summarise tool to get totals, then you need to add the dimensional plug values like Total Region, etc. into the appropriate columns using a formula tool. Then once it mimicks the original structure union it back on and place it in 2nd order in the union options.
The formula step avoids having needless nulls in your data when you union the totals back on; you can use it to plug null values where the original data structure would have natural column values. Just put a union tool and connect both streams together to understand what I am conveying.
Here's a workflow that demonstrates what @Inactive User was referring to
The top branch generates the grand total and appends it the existing dataset. The bottom branch does the same with the subtotals, but uses a RecordID trick to get the subtotals in the correct place. The Summarize tools gets the max record ID for each region and then adds .5 to this to ensure that the subtotal rows follow the corresponding regions. These records are then appended the result of the top branch. The 2 text inputs add in the table names.
The solution is dynamic in that it will handle any number of sub-regions without having to explicitly specify them in separate filter tools.
The result are as follows
Note that the percentages are displayed as decimals, since this the way they were read from the input file.