Add a Sub-total line

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.

Preparation

Accepted answers

ShankerV

Hi @JSB22

One way of doing this.

Many thanks

Shanker V

All comments

ShankerV

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703