Add a Sub-total line

[JSB22]

I have about 10 rows of data and approximately 5 columns.  I need to add sub-totals  at various points along with a Total line at the bottom.  How do I go about doing this?  Thanks!  Joe

I would like to add a line after Cost of Energy that sub-totals Total Operating Revenue and Total Cost of Energy.

[ShankerV · Accepted solution]

Hi @JSB22 

One way of doing this.

Many thanks

Shanker V

[ShankerV]

Hi @JSB22 

Step 1: Input

Step 2: 

IF [Field1]="Subtotal"
THEN [Row-1:Field2]+[Row-3:Field2]
ELSE [Field2]
ENDIF

Many thanks

Shanker V

[caltang]

One more way to add to @ShankerV's solution is to use a Filter tool to get your Totals out with the Contains condition.

Once the totals are out of your True output from Filter tool, use a Summarize tool to sum Field2 column, and you will get your subtotal.

To add it back to your main dataset, use a Record ID tool on your main dataset, and use a Summarize tool thereafter to get the Max RecordID. Then, attach a Formula tool, and change the Max RecordID + 1. From there, use an Append tool to get the Max RecordID + 1 with the subtotal, and rename it as RecordID using a Select tool.

Then, use a Union tool to combine both, and use a Sort tool on RecordID Ascending. 

Adjust this formula and it will be dynamic if you have many rows and/or many groups of subtotals. This can even be made into a Batch Macro.

Hope this adds value!

-Cal

[JSB22]

Shankar, thank you very much for your response.  Your ideas were instrumental in us nearly solving that problem.  See below for my solution based on your instructions.

Per your instructions I added an Input file with the additional line names that needed to be added to my data using a Union tool as shown below.  

My only issue now is that each of these new lines have multiple columns that need to sum on that line. As shown below,  I got the data in the Actual column to sum with the above Expression steps - but not sure how to get my other columns called AOP and Variance to sum down as well.  I tried to add the new columns with and "AND" after my current expresssion but am getting a Parse error.  I will keep plugging away.  Take care, Joe