HI, I am struggling to calculate the Grand Total and Sub Totals for the table i have.] DeptSub DeptSalesCostMfgProfitMKTMKT _ US100101080MKTMKT_CAN2001001575ProdProd_US10226ProdProd_CAN502244 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

Grand Total and Sub Totals

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.

Developer Tools

Accepted answers

Kenda

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!

Totals and Subtotals.yxmd

All comments

Kenda

Totals and Subtotals.yxmd

JoshKushner

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

Flow:

Sub Total:

Grand Total:

Grand Total.yxmd

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:

Gina2021

Thanks tons @Kenda ! I just used this.

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.

verde0903

@KendaAttached, please find a sample. The Grand Total needs to be the variance of the first two sub-totals less the last one.

Thanks,

Maylen

GrandTotal_Calculation.png

Kenda

@verde0903

Is that the exact input you're reading into Alteryx? The solution kind of depends on that and which fields are given vs which are calculated in Alteryx. Assuming the data is exactly as you showed other than the last yellow row, I think the main tricky piece is to isolate the rows you want to add/subtract. This can be done with a filter (to get the rows where column D is empty) and a summarize tool (to get the last row in your data set).

verde0903

Thanks I will try that.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703