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

[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

[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.