Designer| Sub-Total Line (dynamic)

[Mj9715]

Hi All, 

I have a dataset which need to be sub-totaled by col E - "Firm" for the value in column H -"value". The resulting output attached in same file - "Output" tab. 

I also need all the sub-total lines together in one place as shown in tab - "Output (2)" - which is the same as level 2 of sub-total view in excel. 

Please can someone guide? I already took a look at https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-add-a-subtotal-row-to-sum-value... this post and the suggestion isn't dynamic. I need the sub-total creation for all unique firm names instead of me passing the name of the firm in formula tool , since I am likely to have over 30 unique firm names. 

[Christina_H]

What do you want the output from Alteryx to look like?  Just the subtotals?  That's an easy job with a summarize tool.

[iCFO · Accepted solution]

It will be dynamic if you "group by" the Firm Name field for calculating subtotals. The tricky part is to place and space the subtotals correctly. For that you can use a record ids by group, the sum tool to get the "max", and then a formula to add a decimal behind it (like .1) and then sort it to drop it at the bottom row of the data. I also add a "Totals" column during the process. This allows me to reference that field and value for conditional formatting when I am using the reporting tools.

I added a "grand total" at the bottom as well.

[pdave87]

@Mj9715 I believe this will match your expectations of dynamic subtotals inserted in new line with required statement and values. Let me know your thoughts. Same workflow is attached for your test run.

[Mj9715]

Thanks all! @Christina_H , I was looking to add the sub-total line to the data rather than just summarize! 

@iCFO this worked very well, especially the spaces placement worked perfectly, thanks 🙂

@pdave87 This works as well, however the sub total line needed to be placed in column one and also needed a grand total line. Barring that, this solution works!

[iCFO]

@Mj9715 Glad I could help! I also like to insert blank rows to clean up the presentation a bit before I start formatting with the report tools. Let me know if you need help there as well.

[pdave87]

@Mj9715 thanks for sharing this query/exercise. Even though the solution is provided by other SMEs, I stand to be corrected on my previous workflow shared yesterday.

Below workflow covers dynamic subtotals + grand total. Same is attached for your test run.