Sum across row level

[shikhar6339]

Hi All,

I have below records and I need an additional column which tells me the sum across row level. Request your help in resolution.

Data:

A	12	14	14	5	126	64
B	21	67	65	13	3	643
C	321	123	54	79	96	3
D	53	234	85	654	13	52
E	123	34	74	854	957	58

Output required:

A	12	14	14	5	126	64	235
B	21	67	65	13	3	643	812
C	321	123	54	79	96	3	676
D	53	234	85	654	13	52	1091
E	123	34	74	854	957	58	2100

[jrgo · Accepted solution]

Here's a couple options to do this. The 3rd option, not included, is of course to to just use a Formula to add up each field which may be OK if you always have the same amount of fields to add up.

Note that in the Cross Tab tool (first option), there's 2 "Method for Aggregating Values" options selected.

[michelle_mathews · Accepted solution]

Hi @shikhar6339 , two ways this might be accomplished. Example attached!

[adamweaver39]

I think this would be easiest to resolve with a simple Formula tool where you add up each Column!

Otherwise, you could use the Cross Tab and Summarize tool.

[caltang]

I would go with Transpose tool then Cross-Tab tool to ensure that it is dynamic. Because it accounts for future fields/columns that you may need, and it is flexible enough to ignore dropped fields if it changes (change the Transpose tool setting at the bottom of its configuration pane on the left). 

That way you get your horizontal sums dynamically all the time. Refer to @michelle_mathews ' second solution in their attached workflow.

[Qiu]

@shikhar6339 
I also agree with @caltang  @michelle_mathews approach, which is dynamic.
We do need to ntoe that there is a drawback is the name of columns might change after Cross Tab by adding some underscore.
We can use Dynamic Rename tool to bring them back.

[shikhar6339]

Thank you all. I tried using summation of columns via formula but wasn't getting the desired results. It was because some of records had null values. I replaced nulls with 0 and then summation worked.