Grand Total and Sub Totals

[CarlMz]

HI,

I am struggling to calculate the Grand Total and Sub Totals for the table i have.]

Dept	Sub Dept	Sales	Cost	Mfg	Profit
MKT	MKT _ US	100	10	10	80
MKT	MKT_CAN	200	100	15	75
Prod	Prod_US	10	2	2	6
Prod	Prod_CAN	50	2	2	44

I need a subtotal after each Dept , so one after MKT and one after Prod and than one final Grand Total. WHat would be easiest way to achieve this.

C

[Kenda · Accepted solution]

Hey @CarlMz! I would use a Summarize tool to GroupBy Dept then Summing the rest of the fields. Then, to add a Grand Total row, you could add another Summarize tool that is just the Sum of all of the summed fields. You can then use a Union to append it to your original data set. See the attached v11.0 workflow. Hope this helps!

[JoshKushner]

You can use the summarize tool to find sub and grand totals in your data. 

Flow:

Sub Total:

Grand Total:

[JoshKushner]

Totally love how @BarnesK added the total rows to the table. It's a perfect format for document rendering

Flow:

Table:

Table Tool:

Table Styling:

[CarlMz]

Thank you all for the detail breakdown. I really appreciate your help and talking the time out to help me as i learn this amazing tool.

C

[Inactive User]

Hi @joshkushner can you please provide me Workflow? i tried the same way as in Screenshot but i couldnt able to get the desired result.

[Gina2021]

Thanks tons @Kenda ! I just used this.

[gregx]

@JoshKushner a bit old topic, but I believe people should be aware that the solution is incorrect.

It does work, but only on very specific data e.g. assuming the categories are very different to each other or assuming that everyone want it in ascending order.

Adding a word " Total" doesn't guarantee putting the total row on the last position.

e.g.

You have categories "MKT" and "MKT Taco".

After adding " total"  and sorting you get: "MKT", "MKT Taco", "MKT Taco Total", "MKT Total" (WRONG ORDER!)

Also there are other things that can spoil this approach like : we want categories to be in descending order, but Totals on the bottom etc.

concluding, this is wrong.

[verde0903]

@Kenda I used this suggestion and it worked but I need one additional step that am hoping you can assist.  If the Grand Total has to be the difference between MKT Total and Prod Total where would I put that formula in the workflow and how would it have to be written.  Thank you.

[Kenda]

Hi @verde0903 

It depends on how your data looks, so if you could provide a screenshot, that would be helpful. Assuming you just have two rows, each with the total of a department, you could use a multi-row formula tool to calculate the difference. Another option could be to cross tab your data so that the departments are the field names then use a regular formula tool calculate the difference between those two fields.