Forum Discussion
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"
1 Reply
Hi Anonymous ,
Does your actual table have month columns? If it does, you need to pivot plan table or unpivot actual table. The final goal is to let two tables have the same format. Then you can use LOOKUPVALUE() function to get your result.
