Hello everyone, I am trying to add a total row to my dataset as seen below, without using a macro. I would lie to sum the values for each month and all the values below. I saw that you can use transpose and cross tab tool, however the cross tab tool tries to sum my first few columns (region, role, name etc.) although they are string values. I hope that is clear and any help is appreciated, thank you! Input: RegionRoleNameCityCommentsJanFebMarAprMayAmericaManagerAnneBostonnull55005600570058005900APACAnalystJohnMalaysianull45004600470048004900 Output: RegionRoleNameCityCommentsJanFebMarAprMayAmericaManagerAnneBostonnull55005600570058005900APACAnalystJohnMalaysianull45004600470048004900 Total10,00010,20010,40010,60010,800

Adding Total Row to Table

Hello everyone,

I am trying to add a total row to my dataset as seen below, without using a macro. I would lie to sum the values for each month and all the values below. I saw that you can use transpose and cross tab tool, however the cross tab tool tries to sum my first few columns (region, role, name etc.) although they are string values. I hope that is clear and any help is appreciated, thank you!

Input:

Region	Role	Name	City	Comments	Jan	Feb	Mar	Apr	May
America	Manager	Anne	Boston	null	5500	5600	5700	5800	5900
APAC	Analyst	John	Malaysia	null	4500	4600	4700	4800	4900

Output:

Region	Role	Name	City	Comments	Jan	Feb	Mar	Apr	May
America	Manager	Anne	Boston	null	5500	5600	5700	5800	5900
APAC	Analyst	John	Malaysia	null	4500	4600	4700	4800	4900
				Total	10,000	10,200	10,400	10,600	10,800

Workflow

Accepted answers

SPetrie

This is a small variation on the flow @Qiu made. The crosstab tool has an option for adding a total row, so I used that instead of the summarize tool.

example pic.PNG

example.yxmd

All comments

KGT

The4 easiest way to do this is to use a summarise and then union it on.

Use the summarise to create the totals.

Formula to create your field "Comments" that will have the text "Total"

Union with the option at the bottom to select specific order.

The tool at the top in this screenshot is the CReW Macro to do exactly this operation.

Totals Row.yxmd

Qiu

@Bharkavi
I made some changes to the flow of @KGT to be more dynamic.

Totals Row-r1.yxmd

SPetrie

This is a small variation on the flow @Qiu made. The crosstab tool has an option for adding a total row, so I used that instead of the summarize tool.

example pic.PNG

example.yxmd

Qiu

@SPetrie
You are right.👍 Forget about that feature.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703