Calculate Pivot Field

shirinib

New Member
Joined
Jul 24, 2018
Messages
4
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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
ABCDEFGHIJ
1OrderGoal% of TotalForecastStatusRow LabelsSum of GoalSum of ForecastSum of % of Total
2Order 150.15384610BookedAt Risk252538.5%
3Order 2100.15384610To GoBooked253046.2%
4Order 3200.30769220BookedTo Go152030.8%
5Order 4250.38461525At RiskGrand Total6575115.4%
6Order 550.15384610To Go
Sheet12 (2)
Cell Formulas
RangeFormula
C2=D2/SUM($B$2:$B$6)
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,454
Messages
6,178,765
Members
452,875
Latest member
Disastrouscoder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top