Hi Team,
I have Following Data-set
Costs | YEAR 1 | YEAR 2 | YEAR 3 | YEAR 4 | YEAR 5 |
Ex Project Costs 1 | 50 | 0 | 0 | 0 | 0 |
Ex Project Costs 2 | 40 | 0 | 100 | 0 | 0 |
Ex Project Costs 3 | 0 | 60 | 0 | 0 | 0 |
Ex Project Costs 4 | 0 | 0 | 0 | 0 | 0 |
Expected out-put:
Costs | YEAR 1 | YEAR 2 | YEAR 3 | YEAR 4 | YEAR 5 | Total |
Ex Project Costs 1 | 50 | 0 | 0 | 0 | 0 | 50 |
Ex Project Costs 2 | 40 | 0 | 100 | 0 | 0 | 140 |
Ex Project Costs 3 | 0 | 60 | 0 | 0 | 0 | 60 |
Ex Project Costs 4 | 0 | 0 | 0 | 0 | 0 | 0 |
Total | 90 | 60 | 100 | 0 | 0 | 250 |
Solved! Go to Solution.
Hey @BRRLL99, here's a couple of ways of doing this...
1) Use the Add Totals macro, created by @MarqueeCrew and co - http://www.chaosreignswithin.com/p/macros.html
Just tick 'Add Total Row only'
2) Transpose your data and then re-Cross-Tab. Within the CT configuration, there's an option to add a Total Row (you still need your initial aggregation):
I've just used the Dynamic Rename at the end to clean the headers back up. If this is messing with any existing headers then let me know as there's a full work around for maintaining naming:
Hope this helps but please do let us know if you still have any problems!
@BRRLL99
Thank you for the information about the Add Totals macro.
Hi, @BRRLL99
There is 2 easy ways to get your want output:
1- used CreW macro Add-Totals.
2- Transpose + CrossTab.
*********
Please mark it is a solution and give a like with if it help you get want output.
Hi Guys,
Thank you For your Help
I Have used different scenario and got the expected output
No problem @BRRLL99 - for future reference, the Add Totals macro also has an option to add both a total column and row! :)