Hi all! My data looks like this:
| Year | Month | Category A | Category B | Actual | Target |
2023 | 01 | 1 | 1 | 90 | 100 |
| 2023 | 02 | 1 | 1 | 100 | 100 |
| 2023 | 01 | 2 | 2 | 80 | 110 |
Basically, for each Category A and Category B, there is a monthly target and monthly actual values. This target is constant for each month within a year. This table is also linked to a master date table, where the dates are all in a monthly format.
I need the matrix visual to look like this:
| Category A | Category B | Cumulative Target | Difference from Cumulative Target | Jan 2023 | Feb 2023 |
| 1 | 1 | 200 (100 + 100) | -10 ([90+100] - 200) | 90 | 100 |
The hard part here would be cumulative target, since it depends on the number of months being displayed on the matrix. I don't think we can use measures as rows on a matrix chart, so I tried creating a separate table using SUMMARIZE where I got the cumulative target for each Category A and Category B. But this table ignores the date filter.
Would greatly appreciate some help with this!
