Hi Team, Hope everyone is well and safe, Am just trying to add a subtotal by category wise but not happening. Its very simple like below. Can anyone help me ? Raw table : Category2015201620172018Apple2141916Mangoes10222724Banana18303532Tomatoes38505552Onion46586360Peace54667168Ginger62747976 Expected output Category2015201620172018Grand totalApple214191651Mangoes1022272483Banana18303532115Total Fruits30668172249Tomatoes38505552195Onion46586360227Peace54667168259Ginger62747976291Total Vegetable200248268256972Grand total (Fr+Veg)2303143493281221

Add Subtotal and grand total based on the category

Hi Team,

Hope everyone is well and safe,

Am just trying to add a subtotal by category wise but not happening. Its very simple like below. Can anyone help me ?

Raw table :

Category	2015	2016	2017	2018
Apple	2	14	19	16
Mangoes	10	22	27	24
Banana	18	30	35	32
Tomatoes	38	50	55	52
Onion	46	58	63	60
Peace	54	66	71	68
Ginger	62	74	79	76

Expected output

Category	2015	2016	2017	2018	Grand total
Apple	2	14	19	16	51
Mangoes	10	22	27	24	83
Banana	18	30	35	32	115
Total Fruits	30	66	81	72	249
Tomatoes	38	50	55	52	195
Onion	46	58	63	60	227
Peace	54	66	71	68	259
Ginger	62	74	79	76	291
Total Vegetable	200	248	268	256	972
Grand total (Fr+Veg)	230	314	349	328	1221

Batch Macro

Data Investigation

Database Connection

Iterative Macro

Accepted answers

grossal

I made some additional changes to dynamically place the subtotals in the right spot:

Result:

It's a bit sketchy because I use 100 000 000 as a RecordID for the Grand total to place it at the bottom. I expect that you won't have more than 100 Million categories and run into an issue with this.

Updated Workflow attached. Let me know what you think.

Best

Alex

2020-04-26-Add Subtotal and grand total based on the category - updated.yxmd

All comments

dpencheva

Hi I hope my suggestion will help you! I use formula tool to create variable "Is_Fruit" but it can be replace with Join tool or Find and Replace tool if there is a dataset with many records.

Subtotal_and_Grand_Total_DP.yxmd

grossal

Hi @Lakshraj,

I wasn't able to dynamically place the 'Total Fruits' and 'Total Vegetable' in the right spot, but I was able to make it independent of additional years and added an extra Input for easy editing of the Fruits/Vegetables list. It's not really pretty, but I wanted to give it a shot.

Output:

You might be able to get the position right with a couple Filter Tools, but this would make the workflow even more complicated and it already looked unnecessary complicated. Someone else will probably come up with an easier and more dynamic approach, but let me know what you think anyway.