Hey @Polite123, after initially Transposing your data, within the aggregation configuration of the Cross-Tab tool there's an option to add a Total Column (can also add a Total Row if that's needed) and so you can do this in a couple of easy steps. As you're transposing first, this will also be fully dynamic to new fields coming in/existing fields dropping out. The Dynamic Rename at the end is a simple step to clean up the field names:
Add column total as part of the Cross-Tab:
Final product:
Thought I'd mention that, whilst it's slightly less dynamic, there is also an 'Add Totals' macro made by @MarqueeCrew and co which will do this for you with a simple tick box - you just need to tick new columns if they come in:
Have attached my zipped workflow with the macro within.
@Polite123 if you have different combinations of totals that you want to add to your dataset then you can keep the suggested approach with a couple of extra steps. Let's take a couple examples where I want the total of A & C in one field, and the total of A & B in another.
First, I'll filter the transposed data to only leave rows relating to those fields (the other filter is the same but for A/B instead of A/C):
After this, we then Cross-Tab the data as outlined in the response above:
A Dynamic Select tool is used to get rid of everything other than the Total column, as well as the Filename (used for the Join at the end):
The stream used for Code A&B is the same, so we get the same structure of outcome there too:
Now, we just use the Join Multiple tool to bring the original dataset together, along with the varying totals - within the tool, we just remove the duplicated [Filename] field and rename the totals so it's clear which columns they're made up from:
As @Felipe_Ribeir0 mentioned, this approach of first Transposing the data is fully dynamic to changes and so you can see that if we remove the Code A field in the initial data (which is used in both streams), this doesn't cause any errors in the workflow but instead it just doesn't get pulled into any calculations which we would expect:
Hope this helps - in your case, you'd just need a Filter > Cross-Tab > Dynamic Select stream for each calculation of various field combinations you want.
Hola!
Please run the attached workflow and check if it's working.
Edit: I didn't go through all the comments below your post.
Can you talk about the other result columns here? I mean, If you want another column Result-1, which all columns we can expect to get summed up?
I would really appreciate if you can explain the scenario once more.
Here is my solution to the problem
