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 |
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.
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.
@SPetrie
You are right.👍 Forget about that feature.