I have Location and Sales data which is Hierarchical and displayed in a matrix table for that reason (the countries group to continentes etc.). Simplified example below:
| | Q1 | | | Q2 | | |
| | Actual | Plan | Difference | Actual | Plan | Difference |
| France | 699 | 762 | -63 | 489 | 552 | -63 |
| Germany | 440 | 507 | -67 | 463 | 515 | -51 |
| UK | 271 | 313 | -42 | 576 | 618 | -42 |
However i need to show it in the following format with the actual data by quarter, followed by the plan, followed by the calculations for the difference
| | Actual | | Plan | | Difference | |
| | Q1 | Q2 | Q1 | Q2 | Q1 | Q2 |
| France | 699 | 489 | 762 | 552 | -63 | -63 |
| Germany | 440 | 463 | 507 | 515 | -67 | -51 |
| UK | 271 | 576 | 313 | 618 | -42 | -42 |
How i can force the column order to represent table 2, and how i can calculate the difference fields and add them at the end as additional columns (e.g. $ difference and % difference of filtered).
Below is the dataset in original format for testing:
| Region | Country | Product | Quarter | sales |
| Europe | France | Electric | Q1 | 97 |
| Europe | France | Electric | Q2 | 47 |
| Europe | France | Electric | Q3 | 29 |
| Europe | France | Electric | Q4 | 18 |
| Europe | France | ICE | Q1 | 41 |
| Europe | France | ICE | Q2 | 43 |
| Europe | France | ICE | Q3 | 12 |
| Europe | France | ICE | Q4 | 48 |
| Europe | Germany | Electric | Q1 | 71 |
| Europe | Germany | Electric | Q2 | 12 |
| Europe | Germany | Electric | Q3 | 47 |
| Europe | Germany | Electric | Q4 | 18 |
| Europe | Germany | ICE | Q1 | 27 |
| Europe | Germany | ICE | Q2 | 6 |
| Europe | Germany | ICE | Q3 | 93 |
| Europe | Germany | ICE | Q4 | 88 |
| Europe | UK | Electric | Q1 | 28 |
| Europe | UK | Electric | Q2 | 51 |
| Europe | UK | Electric | Q3 | 78 |
| Europe | UK | Electric | Q4 | 2 |
| Europe | UK | ICE | Q1 | 13 |
| Europe | UK | ICE | Q2 | 84 |
| Europe | UK | ICE | Q3 | 7 |
| Europe | UK | ICE | Q4 | 79 |
