Combining Pivot table with other lookup data

[sscornav]

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:

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​

 

[Flyboy65]

You can probably achieve your desired results using Power Pivot. Also, the Difference column in your example seems a bit off.

 

[sscornav]

What I am really looking for is a suggestion for a solution. Thanks for pointing out the error in the difference. Here is the data again:

Region Goals
Region	Goal
North	$ 100,000
South	$ 250,000
East	$ 500,000
West	$ 150,000
Sales Data
Salesperson	Region	Item	Amount	Goal
Joe	North	Car	$ 50,000	100000​
Mary	South	Boat	$ 40,000	250000​
Sally	South	Boat	$ 30,000	250000​
Fredd	East	Car	$ 70,000	500000​
Sam	West	Car	$ 70,000	150000​
Bart	West	Bike	$ 10,000	150000​
Jane	North	Bike	$ 10,000	100000​
Irene	South	Car	$ 80,000	250000​
Pivot Data		Lookup	Math
Row Labels	Amt	Average of Goal	Difference
East	70000​	500000​	$ 430,000
North	60000​	100000​	$ 40,000
South	150000​	250000​	$ 100,000
West	80000​	150000​	$ 70,000
Grand Total	360000​	218750​

 

[sandy666]

your values with $ sign looks like as text

 

[sscornav]

your values with $ sign looks like as text

It's just formating, why focus on that? Does anyone have an idea on how to approach this?