Forum Discussion
Cannot group fields in Pivot Tables
I have merged three datasets - one with actual sales values, one with actual reposession values and one with targets for both. Unfortuately the targets are stored in sharepoint in excel and the others are in a postgres database so its not possible to merge these into one query and get the desired output so i need to provide the following table as an output.
| Actual | Target | Variance | |
| Sales | 268 | 347 | 77.23% |
| Reposessions | 268 | 347 | 77.23% |
there should be a date filter and shop name filter included.
The way the data is structured at present is as follows:
| Shop | Month | Sales | Sales Target | Reposessions | Reposessions target |
| Shop1 | Jan | 20 | 15 | 20 | 15 |
| Shop1 | Feb | 10 | 4 | 10 | 4 |
| Shop1 | Mar | 11 | 15 | 11 | 15 |
| Shop1 | Apr | 15 | 15 | 15 | 15 |
| Shop1 | May | 5 | 56 | 5 | 56 |
| Shop1 | Jun | 6 | 15 | 6 | 15 |
| Shop1 | Jul | 20 | 7 | 20 | 7 |
| Shop1 | Aug | 10 | 15 | 10 | 15 |
| Shop1 | Sep | 11 | 15 | 11 | 15 |
| Shop1 | Oct | 15 | 15 | 15 | 15 |
| Shop1 | Nov | 5 | 9 | 5 | 9 |
| Shop1 | Dec | 6 | 15 | 6 | 15 |
| Shop2 | Jan | 20 | 9 | 20 | 9 |
| Shop2 | Feb | 10 | 15 | 10 | 15 |
| Shop2 | Mar | 11 | 9 | 11 | 9 |
| Shop2 | Apr | 15 | 15 | 15 | 15 |
| Shop2 | May | 5 | 15 | 5 | 15 |
| Shop2 | Jun | 6 | 5 | 6 | 5 |
| Shop2 | Jul | 20 | 15 | 20 | 15 |
| Shop2 | Aug | 10 | 15 | 10 | 15 |
| Shop2 | Sep | 11 | 8 | 11 | 8 |
| Shop2 | Oct | 15 | 15 | 15 | 15 |
| Shop2 | Nov | 5 | 15 | 5 | 15 |
| Shop2 | Dec | 6 | 15 | 6 | 15 |
I have tried a number of options but none have come close to addressing the task. Any suggestions would be much appreciated!!
Thanks
Hi mmccarthy ,
You need to transform table structure in Query Editor first.
Choose columns [Sales], [Sales Target], [Reposessions] and [Reposessions target], click "Unpivot columns".
Add conditional columns.
Apply above changes. In report view mode, add below measure and corresponding fields into a Matrix visual.
Variance = CALCULATE(SUM(Table3[Value]),Table3[Category]="Actual")/CALCULATE(SUM(Table3[Value]),Table3[Category]="Target")
Best regards,
Yuliana Gu
1 Reply
Hi mmccarthy ,
You need to transform table structure in Query Editor first.
Choose columns [Sales], [Sales Target], [Reposessions] and [Reposessions target], click "Unpivot columns".
Add conditional columns.
Apply above changes. In report view mode, add below measure and corresponding fields into a Matrix visual.
Variance = CALCULATE(SUM(Table3[Value]),Table3[Category]="Actual")/CALCULATE(SUM(Table3[Value]),Table3[Category]="Target")
Best regards,
Yuliana Gu