Calculate Pivot Field

[shirinib]

I have three columns in my pivot table. Status, Goal, and Forecast. My rows are Outlook Status (Booked, To Go, At Risk, etc). I want to calculate per status, the % of grand total goal based on forcasted revenue.

Say I have 5 orders
Goal Forecast
Order 1 $5 $10 Booked
Order 2 $10 $10 To Go
Order 3 $20 $20 Booked
Order 4 $25 $25 At Risk
Order 5 $5 $10 To Go
Total $65 $75

Pivot table - Need to figure out how my forecast compares to my goal total.
Status Goal Forecast % of Goal
Booked $25 $30 =30/65 46.2%
To Go $15 $20 =20/65 30.8%
At Risk $25 $25 = 25/65 38.5%
Grand $65 $75 =75/65 115%
Total

Hopefully that makes sense. I've tried all the Show Value As options but haven't figured out the right way. The only thing I've been able to get is a calculated field that's the forecaste/goal per status, not the grand total.

Appreciate any help!

 

[sheetspread]

I don't think you can show another field's grand total as the denominator of a value or calculated pivot field, but with a helper column in the source data:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Order	Goal	% of Total	Forecast	Status		Row Labels	Sum of Goal	Sum of Forecast	Sum of % of Total
2	Order 1	5	0.153846	10	Booked		At Risk	25	25	38.5%
3	Order 2	10	0.153846	10	To Go		Booked	25	30	46.2%
4	Order 3	20	0.307692	20	Booked		To Go	15	20	30.8%
5	Order 4	25	0.384615	25	At Risk		Grand Total	65	75	115.4%
6	Order 5	5	0.153846	10	To Go
Sheet12 (2)

Cell Formulas
Range	Formula
C2	=D2/SUM($B$2:$B$6)

 

[shirinib]

Thanks for feedback. The challenge I have with creating columns H & I, in your sheet, is the totals are dependant on filters (ie region and /or quarter).. I think I need to create my own report template and leverage getpivotdata to pull the grouped data and then formulas for my percent of goal.