PivotTable Percentage A-B

[judgepax]

I have a very large set of data (7000+ rows) that I am using a PivotTable to summarize. Simplifed, PivotTable looks like following. "Revenue" and "Expenses" were in the raw 7000-row data, and I created a PivotTable "Calculated Field" for both "Margin$" and ""Margin%". Issue I am having is showing the "Margin%" Variances.

Good example of the problem is Frisco's Apples: for now the PivotTable is giving me a -10% Margin% (-20 Margin / 200 Revenue) but what I want the Variance Margin% to show is -5% (10% Actual - 15% Budget).

PivotTable is working perfect for everything else so I am hoping a bright mind out there will know how to keep the Margin% as Revenue/Expenses for Actual and Budget, but Margin% as Actual-Budget for Variance.

Thanks, y'all!

		Actual Apples	Actual Bananas	Actual Oranges	Budget Apples	Budget Bananas	Budget Oranges	Variance Apples	Variance Bananas	Variance Oranges
Frisco	Revenue	1000	1500	2000	800	600	1400	200	900	600
	Expense	900	1425	1720	680	546	1176	220	879	544
	Margin$	100	75	280	120	54	224	-20	21	56
	Margin%	10%	5%	14%	15%	9%	16%
Denver	Revenue	1000	1200	3000	1000	600	3200	0	600	-200
	Expense	920	1080	2430	850	546	2688	70	534	-258
	Margin$	80	120	570	150	54	512	-70	66	58
	Margin%	8%	10%	19%	15%	9%	16%
Atlanta	Revenue	1000	300	5000	1200	600	3600	-200	-300	1400
	Expense	850	285	4450	1020	546	3024	-170	-261	1426
	Margin$	150	15	550	180	54	576	-30	-39	-26
	Margin%	15%	5%	11%	15%	9%	16%
Grand Total	Revenue	3000	3000	10,000	3000	1800	8200	0	1200	1800
	Expense	2670	2790	8600	2550	1638	6888	120	1152	1712
	Margin$	330	210	1400	450	162	1312	-120	48	88
	Margin%	11%	7%	14%	15%	9%	16%

<TBODY>
</TBODY>

 

[alfranco17]

If you are using Excel 2010 or 2013 Pro you could use Power Pivots for this kind of calculation. Here's a link to something similar I asked on the power pivot fórum. http://www.mrexcel.com/forum/powerpivot-questions/697043-percentage-based-parent-total.html