Add Subtotal and grand total based on the category

[Lakshraj]

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

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

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

Workflow attached.

Best

Alex

[grossal · Accepted solution]

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

[Lakshraj]

Amazing thank you so much , it worked. :). i have one more quick request - my formula column is updating even in blank cells. Is there a way to resolve it ?

Like I used "If" condition and Else means update as "XXX". So these "XXX" is getting updated in blank rows as well.

[grossal]

I would do it the way you suggested it:

IF isnull([Your Column])
THEN XXX
ELSE [Your Column]
ENDIF

Make sure the datatype of your XXX is the same as the rest of the column, but that's it.

Alex

[dinesha]

i have added one another data set with group id