Calculate error or Data format issue?
I have two data tables:
Monthly Targets
Grid Gate Month Plan
| San Joan | 6. Installed | January | 39 |
| Orange | 6. Installed | January | 18 |
| North | 6. Installed | January | 22 |
| West | 6. Installed | January | 71 |
| East | 6. Installed | January | 92 |
| High | 6. Installed | January | 84 |
| Central | 6. Installed | January | 15 |
| San Joan | 5. Rel to Construction not yet Installed | January | 311 |
| Orange | 5. Rel to Construction not yet Installed | January | 182 |
| North | 5. Rel to Construction not yet Installed | January | 358 |
| West | 5. Rel to Construction not yet Installed | January | 381 |
| East | 5. Rel to Construction not yet Installed | January | 228 |
| Highland | 5. Rel to Construction not yet Installed | January | 621 |
| Central | 5. Rel to Construction not yet Installed | January | 329 |
| San Joan | 4. Planning Complete not Released to Construction | January | 446 |
| Orange | 4. Planning Complete not Released to Construction | January | 518 |
| North | 4. Planning Complete not Released to Construction | January | 634 |
| West | 4. Planning Complete not Released to Construction | January | 588 |
| East | 4. Planning Complete not Released to Construction | January | 240 |
| High | 4. Planning Complete not Released to Construction | January | 157 |
| Central | 4. Planning Complete not Released to Construction | January | 493 |
| San Joan | 2. Sent to Grid for Planning, not yet Planned | January | 852 |
| Orange | 2. Sent to Grid for Planning, not yet Planned | January | 1441 |
| North | 2. Sent to Grid for Planning, not yet Planned | January | 631 |
| West | 2. Sent to Grid for Planning, not yet Planned | January | 292 |
| East | 2. Sent to Grid for Planning, not yet Planned | January | 546 |
| High | 2. Sent to Grid for Planning, not yet Planned | January | 1964 |
| Central | 2. Sent to Grid for Planning, not yet Planned | January | 611 |
And Actual
Grid 2. Sent to Grid for Planning, not yet Planned 4. Planning Complete not Released to Construction 5. Rel to Construction not yet Installed 6. Installed
| West | 1127 | 749 | 710 | 381 |
| East | 1333 | 1036 | 965 | 349 |
| North Coast | 1162 | 1025 | 994 | 480 |
| Orange | 601 | 382 | 377 | 147 |
| Central | 3328 | 2481 | 1993 | 770 |
| San Joan | 1068 | 593 | 560 | 244 |
| Central | 1569 | 919 | 845 | 455 |
Note: The Plan table has Jan-Dec on it but I cut it off.
On my report page I have a month slicer and am able to successfully graph the plans next to the actuals and the plans adjust according to the month filter.
I'm trying to create a variance measurement between plan (by month) and actual.
My problem is I can't create a measure or column filter for the gates. I've tried using CALCULATE, SUMX, FILTER(S), and LOOKUPVALUE. The problem appears to be that there's 12 values for each gate and grid. Am I going about this wrong? Should the data be formatted differently?
I'm trying to show "Here we are this month, here is where we are compared to next month's plan"
