I have a pivot table that contains sales data including region. I have a separate table that contains the sales goals by region and changes periodically. My current solution is to place the goals outside of the sales pivot data, but as the data changes the two become misaligned. (perhaps more regions are added) Does anyone have a technique that would put the goal data in the pivot without replicating it?
Here is a simple example:
Here is a simple example:
Region Goals | |||
Region | Goal | ||
North | $ 100,000 | ||
South | $ 250,000 | ||
East | $ 500,000 | ||
West | $ 100,000 | ||
Sales Data | |||
Salesperson | Region | Item | Amount |
Joe | North | Car | $ 50,000 |
Mary | South | Boat | $ 40,000 |
Sally | South | Boat | $ 30,000 |
Fredd | East | Car | $ 70,000 |
Sam | West | Car | $ 70,000 |
Bart | West | Bike | $ 10,000 |
Jane | North | Bike | $ 10,000 |
Irene | South | Car | $ 80,000 |
Pivot Data | Lookup | Math | |
Row Labels | Amt | Goal | Difference |
East | 70000 | $ 500,000 | $ 430,000 |
North | 60000 | $ 100,000 | $ 30,000 |
South | 150000 | $ 250,000 | $ 180,000 |
West | 80000 | $ 100,000 | $ 30,000 |
Grand Total | 360000 |