Subtotal multiple Rows

[Sjlosey]

Hello there,

I am looking to learn how to replicate subtotaling in excel and how I can translate that into an Alteryx Workflow. For each change in store i need to add a subtotal line that calculates different formulas for different sets of data.

So on a new subtotaled line per store break I need a sum of Housholds, an average of income, and other sums and averages. Any guidance would be super helpful, thank you!

[JohnJPS]

Could you send it through multiple Summary tools, grouping at the appropriate levels, then join these back to the original dataaset? That will get you the breakdowns.  From there it's just formatting.

[PaulT · Accepted solution]

Greetings,

I threw together a quick sample with some fake data.

Let me know if this helps!

[Sjlosey]

Thanks guy! I was apparently way over complicating what i needed to do. The sum tool and union was what I was looking for.

Thanks again!

[axn]

Hi Paul,

How did you configure your union tool so that the summary tool output didn't just all get unioned to the bottom of the original data? When I try to replicate your workflow, my summarized data just gets tacked on to the bottom of my original data after the union tool.

[BenMoss]

Hi axn,

I just had a quick look at Pauls workflow and fiddled around with the configuration of the 'set specific output order'. With his workflow it appears that when no specific order is given (i.e. it is unchecked), the records get sorted as appropriate, with the sub totals nested in the right place, whereas when the 'set specific output order' is checked, the results get stacked completely at the top or bottom depending on what you have set your output order too.

If this doesnt work you could of course just throw in a sort tool after the union, and sort by the field that you grouped by within your union. In the case of Pauls workflow, this is City.

Hope this helps,

Ben

[LStevenson]

Adding the sort tool after the union cleared up my problem which was this- one item from the next group was being added to the previous group. It is all good now! Thanks!

[djcharon1]

Also, if you are grouping/sorting by numbers for some reason, whether they are text or numeric, select the "Use Dictionary Order" box in the Sort too.