Forum Discussion
Solved
Need help with creating a calculated variance column in Matrix
Hi everyone.
Currently I have a dataset which contains the volume of product by date and grade, in rows. I would like to display this in a Matrix and allow the user to slice and dice based on grade of product. The real question/problem I have is, I would like to add a variance column to compare the difference between two rows of data based on date. See my data sample below and my desired Matrix. Ideally, I would like to be able to filter accordingly, and in this case, my default filter is set to 'Export'.
Raw Data
| Product | Grade | Date | Volume |
| Apples | Export | 27/10/2022 | 100 |
| Oranges | Export | 27/10/2022 | 120 |
| Pears | Export | 27/10/2022 | 130 |
| Bananas | Export | 27/10/2022 | 80 |
| Apples | Local | 27/10/2022 | 120 |
| Oranges | Local | 27/10/2022 | 40 |
| Pears | Local | 27/10/2022 | 50 |
| Bananas | Local | 27/10/2022 | 100 |
| Apples | Export | 28/10/2022 | 120 |
| Oranges | Export | 28/10/2022 | 40 |
| Pears | Export | 28/10/2022 | 50 |
| Bananas | Export | 28/10/2022 | 100 |
| Apples | Local | 28/10/2022 | 100 |
| Oranges | Local | 28/10/2022 | 120 |
| Pears | Local | 28/10/2022 | 130 |
| Bananas | Local | 28/10/2022 | 80 |
Desired Matrix
Note: Slicer is set to Export
| Product | 27/10/2022 | 28/10/2022 | Volume Diff | % Diff |
| Apples | 100 | 120 | 20 | 20% |
| Oranges | 120 | 40 | -80 | -67% |
| Pears | 130 | 50 | -80 | -62% |
| Bananas | 80 | 100 | 20 | 25% |
whereby
Product is ROWS
Date is Columns
Volume is Values
Hope this makes sense and thanks in advance for your help.
Cheers,JL
File attached...
1 Reply
daXtremeSolution Sage
File attached...