Forum Discussion
dynamic diff calculation row in matrix
Dear Power BI Gurus ! I'm struggling for some time already with creating of dynamic calculation which would return difference between selected rows in matrix. Here's a sample of raw data: ...
here's the sample data:
| Forecast ID | Company | Product type | Date | Qty |
| Actuals | Comp1 | A | Jan 2024 | 8 |
| Actuals | Comp1 | A | Feb 2024 | 6 |
| Actuals | Comp1 | B | Apr 2024 | 5 |
| Actuals | Comp1 | A | Feb 2024 | 2 |
| Actuals | Comp2 | Y | Jan 2024 | 10 |
| Actuals | Comp2 | X | Jan 2024 | 4 |
| Actuals | Comp2 | X | Mar 2024 | 10 |
| Actuals | Comp2 | X | Jan 2024 | 5 |
| Actuals | Comp3 | Z | May 2024 | 6 |
| Forecast 1 | Comp1 | A | Jan 2024 | 10 |
| Forecast 1 | Comp1 | B | Jan 2024 | 15 |
| Forecast 1 | Comp2 | Y | Jan 2024 | 5 |
| Forecast 1 | Comp2 | X | Jan 2024 | 2 |
| Forecast 1 | Comp2 | X | Feb 2024 | 7 |
| Forecast 1 | Comp2 | Y | Apr 2024 | 10 |
| Forecast 1 | Comp2 | X | Apr 2024 | 5 |
| Forecast 2 | Comp2 | Y | May 2024 | 10 |
| Forecast 1 | Comp3 | Z | Apr 2024 | 3 |
| Forecast 2 | Comp1 | B | Jan 2024 | 11 |
| Forecast 2 | Comp1 | A | Feb 2024 | 3 |
| Forecast 2 | Comp1 | A | Feb 2024 | 10 |
| Forecast 2 | Comp2 | X | Jan 2024 | 6 |
| Forecast 2 | Comp2 | X | Feb 2024 | 5 |
| Forecast 2 | Comp2 | X | Mar 2024 | 4 |
| Forecast 2 | Comp2 | Y | Jan 2024 | 4 |
| Forecast 2 | Comp3 | Z | Apr 2024 | 10 |
| Forecast 3 | Comp2 | Y | May 2024 | 6 |
| Forecast 3 | Comp3 | Z | Apr 2024 | 6 |
| Forecast 3 | Comp1 | B | Jan 2024 | 6 |
| Forecast 3 | Comp1 | A | Feb 2024 | 10 |
| Forecast 3 | Comp1 | A | Feb 2024 | 12 |
| Forecast 3 | Comp2 | X | Jan 2024 | 10 |
| Forecast 3 | Comp2 | X | Feb 2024 | 4 |
| Forecast 3 | Comp2 | X | Mar 2024 | 4 |
| Forecast 3 | Comp2 | Y | Jan 2024 | 4 |
| Forecast 3 | Comp3 | Z | Apr 2024 | 6 |
Then in the matrix I would like to see following information as in below sample:
| sample 1 | date hierarchy | |||||
| Q1 | ||||||
| Jan | Feb | Mar | Total | |||
| Company | Product type | Forecast ID | ||||
| Comp2 | ||||||
| X | ||||||
| Actuals | 9 | 10 | 19 | |||
| Forecast 1 | 2 | 7 | 9 | |||
| Forecast 2 | 6 | 5 | 4 | 15 | ||
| Forecast variance | 4 | -2 | 4 | 6 | ||
| date hierarchy | ||||||
| sample 2 | ||||||
| Q1 | ||||||
| Jan | Feb | Mar | Total | |||
| Company | Product type | Forecast ID | ||||
| Comp2 | ||||||
| X | ||||||
| Actuals | 9 | 10 | 19 | |||
| Forecast 1 | 2 | 7 | 9 | |||
| Forecast 3 | 10 | 4 | 4 | 18 | ||
| Forecast variance | 8 | -3 | 4 | 9 |
By using a slicer I would like to be able to pick whatever Forecast ID I want, visualize them in rows (as shown in sample) and the last row would be the dynamic one measuring a diff between those two Forecast IDs. Forecast ID called "Actuals" will be visualized in first row but only as a reference and not to be taken into any calculation.
I hope my explanation is clear enough.
Thank You for any suggestions and ideas.
