Hello all,
I'm wishing to run a pivot table on data such that the row totals all add to be 100%. Unfortunately, when I select "Show data as "% of Row Total", the pivot table fills with 100%.
What I'm trying to achieve is shown in the second table. Any ideas how I can do so?
Thanks!
I'm wishing to run a pivot table on data such that the row totals all add to be 100%. Unfortunately, when I select "Show data as "% of Row Total", the pivot table fills with 100%.
What I'm trying to achieve is shown in the second table. Any ideas how I can do so?
| Response ID | Region | Option 1 | Option 2 | Option 3 | Option 4 | Option 5 | Option 6 | Option 7 | Option 8 | Option 9 | Option 10 | Option 11 | Option 12 |
| 44 | North Africa | 3 | 3 | 2 | 3 | 3 | 1 | 1 | 3 | 3 | 3 | 3 | 3 |
| 49 | South America | 2 | 1 | 1 | 1 | 2 | 2 | 3 | 3 | 3 | |||
| 66 | North America | 1 | 3 | 1 | 2 | 2 | 1 | 1 | 3 | 2 | 2 | 2 | |
| 98 | North America | 1 | 2 | 1 | 2 | 1 | 2 | 1 | 1 | 2 | 2 | 3 | |
| 106 | North America | 3 | 3 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 3 | 2 | |
| 147 | North America | 3 | 3 | 2 | 3 | 1 | 3 | 1 | 1 | 1 | |||
| 163 | West Africa | 1 | 3 | 1 | 3 | 1 | 1 | 1 | 2 | 3 | 3 | 1 | 3 |
| 167 | South America | 1 | 3 | 1 | 1 | 3 | 1 | 3 | |||||
| 195 | West Africa | 2 | 1 | 2 | 1 | 1 | 2 | ||||||
| 211 | South America | 2 | 3 | 2 | 2 | 1 | 2 | 1 | 2 | 3 | 3 | 2 | 3 |
| 219 | West Africa | 2 | 3 | 2 | 3 | 3 | 2 | 1 | 3 | 3 | 3 | 1 | 3 |
| 238 | West Africa | 1 | 3 | 2 | 3 | 2 | 2 | 1 | 2 | 3 | 3 | 3 | 3 |
| 241 | North Africa | 3 | 2 | 1 | 1 | 3 | 3 | 1 | 3 | ||||
| 249 | West Africa | 3 | 2 | 3 | 1 | 3 | 3 | 2 | 3 | ||||
| 252 | West Africa | 3 | 3 | 2 | 1 | 1 | 3 | 3 | 2 | 3 | |||
| 258 | West Africa | 1 | 2 | 1 | 1 | 3 | 2 | 2 | 3 | ||||
| 276 | West Africa | 3 | 1 | 3 | 1 | 3 | 3 | 3 | 3 | ||||
| 293 | West Africa | 1 | 3 | 1 | 1 | 1 | 1 | 1 | 1 | 3 | 1 | 1 | 3 |
| 312 | North Africa | 1 | 1 | 3 | 1 | 1 | 1 | 1 | 1 | 1 | |||
| 326 | North Africa | 3 | 1 | 1 | 1 | 2 | 3 | 2 | 1 | 3 | |||
| 340 | North Africa | 2 | 3 | 2 | 3 | 3 | 3 | 1 | 2 | 3 | 3 | 2 | 3 |
| 349 | South Asia | 1 | 3 | 2 | 2 | 1 | 1 | 1 | 1 | 3 | 3 | 2 | 3 |
| 351 | North Africa | 1 | 3 | 1 | 3 | 1 | 1 | 1 | 3 | 2 | 3 | 3 | |
| 353 | North Africa | 3 | 3 | 1 | 3 | 2 | 3 | ||||||
| 367 | West Africa | 3 | 2 | 3 | 1 | 1 | 2 | 3 | 3 | 2 | 3 | ||
| 368 | North Africa | 3 | 2 | 2 | 3 | 1 | 3 | 1 | 1 | 3 | |||
| 386 | South America | 3 | 1 | 1 | 1 | 3 | 1 | 1 | 3 | 3 | 2 | 3 | |
| 389 | West Africa | 3 | 1 | 1 | 1 | 2 | 2 | 1 | 3 | ||||
| 412 | North Africa | 3 | 1 | 1 | 1 | 2 | 1 | 3 | |||||
| 420 | South Asia | 1 | 3 | 1 | 2 | 1 | 2 | 3 | 2 | 2 | 3 | ||
| 441 | North Africa | 1 | 3 | 1 | 1 | 1 | 1 | 1 | 1 | 3 | 3 | 3 | 3 |
| 451 | West Africa | 3 | 1 | 3 | 1 | 1 | 3 | 3 | 2 | 3 | |||
| 477 | West Africa | 2 | 3 | 1 | 3 | 2 | 2 | 1 | 3 | 3 | 3 | 3 | 3 |
| 504 | South America | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 3 | 3 | 2 | 3 | |
| 519 | North Africa | 3 | 2 | 1 | 2 | 1 | 2 | 1 | 1 | 2 | 1 | 1 | 3 |
| 577 | North Africa | 3 | 1 | 1 | 1 | 2 | 1 | 3 | 3 | 3 | 1 | 3 | |
| 601 | South America | 3 | 2 | 1 | 1 | 1 | 3 | 2 | 1 | 3 | |||
| 625 | South Asia | 2 | 3 | 2 | 2 | 2 | 3 | 1 | 3 | 3 | 3 | 2 | 3 |
| 626 | South Asia | 3 | 3 | 2 | 1 | 2 | 1 | 2 | 3 | 3 | 3 | 3 | |
| 629 | South Asia | 3 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | ||||
| 634 | South Asia | 3 | 3 | 2 | 2 | 3 | 1 | 1 | 2 | 2 | 1 | 1 | 3 |
| 668 | South Asia | 3 | 3 | 1 | 1 | 1 | 1 | 3 | 3 | 1 | 3 | ||
| 674 | North Africa | 1 | 3 | 1 | 3 | 1 | 2 | 1 | 1 | 3 | 2 | 2 | 3 |
| 692 | North America | 2 | 1 | 3 | 2 | 2 | 1 | 2 | 1 | 1 | 1 | ||
| 693 | North America | 3 | 3 | 3 | 3 | 2 | 2 | 1 | 3 | 3 | 2 | 3 | 3 |
| 707 | South America | 2 | 3 | 2 | 1 | 2 | 2 | 1 | 1 | 3 | 2 | 2 | 3 |
| 727 | South Asia | 3 | 3 | 2 | 3 | 2 | 1 | 2 | 3 | 2 | 2 | 3 | |
| 759 | South Asia | 2 | 3 | 3 | 3 | 2 | 2 | 1 | 2 | 1 | 3 | 2 | 3 |
| 760 | North Africa | 3 | 3 | 2 | 3 | 1 | 2 | 1 | 2 | 3 | 3 | 3 | 3 |
| 782 | West Africa | 3 | 1 | 1 | 1 | 3 | 2 | 1 | 3 | ||||
| 801 | West Africa | 2 | 3 | 2 | 3 | 3 | 2 | 1 | 3 | 3 | 3 | 3 | 3 |
| 821 | South Asia | 2 | 3 | 2 | 2 | 2 | 1 | 3 | 3 | 3 | 3 | 3 | |
| 826 | South Asia | 3 | 3 | 2 | 1 | 1 | 1 | 1 | 3 | 3 | 2 | 3 | |
| 833 | South Asia | 2 | 3 | 1 | 3 | 2 | 2 | 1 | 1 | 3 | 3 | 2 | 3 |
| 846 | North America | 3 | 3 | 1 | 1 | 2 | 2 | ||||||
| 854 | South Asia | 1 | 1 | 1 | 3 | 1 | 1 | 3 | 3 | 3 | 1 | 3 | |
| 856 | South Asia | 3 | 2 | 1 | 2 | 1 | 1 | 2 | 3 | 2 | 3 | 3 | |
| 874 | South Asia | 2 | 2 | 2 | 1 | 2 | 2 | 2 | 1 | 2 | |||
| 900 | North America | 2 | 3 | 1 | 3 | 2 | 1 | 1 | 1 | 2 | 2 | 1 | 3 |
| 904 | North America | 2 | 3 | 2 | 2 | 1 | 2 | 1 | 3 | 3 | 2 | 3 | 3 |
| 914 | North America | 3 | 2 | 3 | 1 | 2 | 3 | 1 | 3 | 2 | 3 | 2 | 3 |
| 929 | North Africa | 2 | 3 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 3 | |
| 934 | West Africa | 1 | 3 | 1 | 3 | 1 | 3 | 1 | 1 | 3 | 3 | 3 | 3 |
| 938 | South Asia | 2 | 3 | 1 | 2 | 1 | 1 | 2 | 3 | 2 | 2 | 3 | |
| 940 | West Africa | 3 | 2 | 3 | 2 | 3 | 1 | 1 | 3 | 3 | 3 | 3 | |
| 963 | South Asia | 1 | 3 | 1 | 1 | 3 | |||||||
| 968 | West Africa | 3 | 2 | 3 | 2 | 1 | 2 | 3 | 3 | 2 | 3 | ||
| 976 | North Africa | 3 | 3 | 2 | 1 | 1 | 2 | 3 | 2 | 3 | |||
| 992 | South Asia | 3 | 3 | 2 | 1 | 1 | 1 | 2 | 3 | 2 | 3 | ||
| 999 | North Africa | 3 | 2 | 2 | 3 | 2 | 2 | 1 | 2 | 3 | 1 | 2 | 2 |
| 1005 | North Africa | 2 | 3 | 2 | 1 | 2 | 3 | 1 | 2 | 3 | 2 | 2 | 3 |
| 1024 | North Africa | 3 | 3 | 1 | 2 | 1 | 1 | 3 | 3 | 3 | 3 | ||
| 1047 | West Africa | 1 | 3 | 1 | 1 | 1 | 1 | 1 | 1 | 3 | 3 | 1 | 3 |
| 1054 | North Africa | 3 | 1 | 3 | 1 | 1 | 2 | 2 | 2 | 2 | 3 | ||
| 1063 | North America | 2 | 3 | 1 | 2 | 1 | 2 | 1 | 2 | 3 | 2 | 2 | 2 |
| 1067 | North Africa | 3 | 1 | 3 | 3 | 1 | 3 | 1 | 3 | 3 | |||
| 1078 | North America | 2 | 1 | 2 | 3 | 3 | 3 | 1 | 3 | 2 | 2 | 2 | 1 |
| 1086 | North Africa | 3 | 1 | 2 | 1 | 2 | 1 | 1 | 3 | 2 | 2 | 3 | |
| 1109 | West Africa | 3 | 2 | 3 | 2 | 1 | 3 | 3 | 3 | 3 | |||
| 1129 | South Asia | 1 | 3 | 2 | 3 | 2 | 3 | 1 | 2 | 3 | 3 | 3 | 3 |
| 1133 | North America | 3 | 3 | 3 | 2 | 3 | 2 | 1 | 2 | 3 | 1 | 1 | 3 |
| 1193 | North America | 3 | 2 | 3 | 1 | 1 | 3 | 2 | 1 | ||||
| 1212 | South Asia | 3 | 1 | 1 | 1 | 1 | 3 | 3 | 2 | 3 | |||
| 1213 | North Africa | 2 | 3 | 2 | 1 | 3 | 2 | 1 | 2 | ||||
| 1321 | Southeast Asia | 2 | 2 | 1 | 1 | 2 | 1 | 1 | 1 | 2 | 2 | 1 | 1 |
| 2109 | East Europe | 3 | 1 | 1 | 1 | 1 | 1 | 1 | 3 | 2 | 1 | 3 | |
| 2162 | East Europe | 1 | 3 | 3 | 3 | 1 | 1 | 1 | 1 | 3 | 3 | 2 | 3 |
| 2264 | East Europe | 3 | 2 | 2 | 3 | 3 | 3 | 1 | 2 | 2 | 3 | 2 | 3 |
| 2276 | East Europe | 1 | 3 | 1 | 1 | 1 | 3 | 3 | 3 | ||||
| 2341 | Southeast Asia | 1 | 3 | 1 | 3 | 1 | 1 | 1 | 2 | 3 | 2 | 2 | 3 |
| 2352 | Southeast Asia | 1 | 3 | 1 | 1 | 1 | 1 | 1 | 1 | 3 | 3 | 3 | 3 |
| 2368 | Southeast Asia | 3 | 3 | 2 | 2 | 3 | 1 | 3 | 2 | 3 | 3 | ||
| 2406 | Southeast Asia | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 3 | 3 | 3 | 1 | 3 |
| 2407 | East Europe | 2 | 2 | 1 | 2 | 2 | 1 | 1 | 2 | 3 | 2 | 2 | 3 |
| 2415 | East Europe | 1 | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 3 | 1 | 3 | |
| 2439 | East Europe | 1 | 1 | 1 | 2 | 3 | |||||||
| 2463 | East Europe | 1 | 3 | 1 | 1 | 1 | 3 | 1 | 2 | 3 | |||
| 2487 | East Europe | 1 | 1 | 1 | 3 | 1 | 3 | ||||||
| 2517 | East Europe | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 3 | 1 | 3 | |
| 2574 | Southeast Asia | 3 | 3 | 3 | 1 | 3 | 3 | 3 | |||||
| 2634 | Southeast Asia | 2 | 3 | 1 | 2 | 2 | 3 | 1 | 2 | 3 | 2 | 3 | 3 |
Thanks!
