Pivot Table-calculated field based on 2 pivot table fields

[smk511]

Hi
I apologize if this becomes hard to follow, I am very new to posting in things like this so very well may not give all of the right info or have all of the correct terminology. I use your forum regularly for help as I am learning Excel, but couldn't find a solution to this.

I am trying to create a % within a pivot table that will be based on 2 other fields that are already in the pivot table. One of the columns is a count and the other is a sum, here is what the pivot table looks like. I need the % Closed to be Qty won divided by Qty Quoted so we can show a close rate.
Qty won is a 'sum' field where I have an if formula in the source data saying basically 'if a quote was won, show 1, if not show zero' so then Qty won in the pivot table is summing the '1s'. The Qty Quoted is a more standard count field in the pivot table based on the Month Quoted (which is the Month shown) simply counting how many times a quote appears in that Month.

I have tried several ways to add the calculated field, but it keeps giving me the same information (as below).

I appreciate any help.

Thanks
smk511

[TABLE="width: 557"]
<tbody>[TR]
[TD]Row Labels
[/TD]
[TD]Qty Quoted
[/TD]
[TD]Qty won
[/TD]
[TD]Revenue Quoted
[/TD]
[TD]Revenue Won
[/TD]
[TD]Count of % closed
[/TD]
[/TR]
[TR]
[TD]January
[/TD]
[TD="align: right"]185
[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]$571,498
[/TD]
[TD="align: right"]$182,788
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]February
[/TD]
[TD="align: right"]166
[/TD]
[TD="align: right"]16
[/TD]
[TD="align: right"]$427,046
[/TD]
[TD="align: right"]$107,277
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]March
[/TD]
[TD="align: right"]182
[/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]$915,335
[/TD]
[TD="align: right"]$172,468
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]April
[/TD]
[TD="align: right"]209
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]$550,588
[/TD]
[TD="align: right"]$88,816
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]May
[/TD]
[TD="align: right"]193
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]$682,574
[/TD]
[TD="align: right"]$190,881
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]June
[/TD]
[TD="align: right"]164
[/TD]
[TD="align: right"]19
[/TD]
[TD="align: right"]$684,195
[/TD]
[TD="align: right"]$154,668
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]July
[/TD]
[TD="align: right"]211
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]$752,461
[/TD]
[TD="align: right"]$265,622
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]August
[/TD]
[TD="align: right"]265
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]$1,078,066
[/TD]
[TD="align: right"]$168,142
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]September
[/TD]
[TD="align: right"]214
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]$1,006,264
[/TD]
[TD="align: right"]$183,330
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]October
[/TD]
[TD="align: right"]202
[/TD]
[TD="align: right"]24
[/TD]
[TD="align: right"]$996,062
[/TD]
[TD="align: right"]$101,103
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]November
[/TD]
[TD="align: right"]196
[/TD]
[TD="align: right"]19
[/TD]
[TD="align: right"]$985,644
[/TD]
[TD="align: right"]$151,458
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]December
[/TD]
[TD="align: right"]171
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]$566,552
[/TD]
[TD="align: right"]$0
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD="align: right"]2358
[/TD]
[TD="align: right"]301
[/TD]
[TD="align: right"]$9,216,286
[/TD]
[TD="align: right"]$1,766,552
[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Smitty]

Welcome to the Board!

It should just be % Won/% Quoted.

Can you post your calculation?

 

[smk511]

Thanks Smitty

My formula currently is ='Month Won'/'Month Quoted' (which obviously isnt working)
When I go in to create a calculated field it only gives me the fields that are in my Source Data....not the ones that are already calcuated in the pivot table.
Below is a sample of my source data.
Qty Quoted is a COUNT on the Quote # Column (C)
Qty Won is a SUM on the Quote Won? Column (M)

Ive been known to overcomplicate things for the sake of just using what I know, so please let me know if I am going about this wrong.

Thanks
Shante


[TABLE="width: 1756"]
<tbody>[TR]
[TD]Customer #[/TD]
[TD]Company Name[/TD]
[TD]Quote #[/TD]
[TD]Quoted Date[/TD]
[TD]Total[/TD]
[TD]AE[/TD]
[TD]AR[/TD]
[TD]Estimator[/TD]
[TD]AE Comment[/TD]
[TD]Order Date[/TD]
[TD]Total Job[/TD]
[TD]Won Total[/TD]
[TD]Quote won?[/TD]
[TD]For % Calc[/TD]
[TD]Month Quoted[/TD]
[TD]Year Quoted[/TD]
[TD]Month Won[/TD]
[TD]Year Won[/TD]
[/TR]
[TR]
[TD]3722[/TD]
[TD]Czarnowski Pittsburgh[/TD]
[TD]003-605-C[/TD]
[TD]1/28/2013[/TD]
[TD]$3.50 [/TD]
[TD]Kevin Okon[/TD]
[TD]Jaime Henrickson[/TD]
[TD]Dan Stefl[/TD]
[TD] [/TD]
[TD="align: right"]2/4/2013[/TD]
[TD="align: right"]$853[/TD]
[TD]$853[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]January[/TD]
[TD="align: right"]2013[/TD]
[TD]February[/TD]
[TD="align: right"]2013[/TD]
[/TR]
[TR]
[TD]1607[/TD]
[TD]Visual Marketing Mentors, Llc[/TD]
[TD]004-565-G[/TD]
[TD]11/12/2013[/TD]
[TD]$264.37 [/TD]
[TD]Tony Ricci[/TD]
[TD]Shante Katinsky[/TD]
[TD]Dan Stefl[/TD]
[TD] [/TD]
[TD]NA[/TD]
[TD="align: right"]$0[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]November[/TD]
[TD="align: right"]2013[/TD]
[TD]November[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]374[/TD]
[TD]Vision Exhibits, Inc[/TD]
[TD]005-829-C[/TD]
[TD]5/30/2013[/TD]
[TD]$5,115.75 [/TD]
[TD]Open Territory[/TD]
[TD]Kelly Reeder[/TD]
[TD]Dan Stefl[/TD]
[TD] [/TD]
[TD]NA[/TD]
[TD="align: right"]$3,362[/TD]
[TD]$3,362[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]May[/TD]
[TD="align: right"]2013[/TD]
[TD]May[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Dimensional Dynamics[/TD]
[TD]007-880-C[/TD]
[TD]4/2/2013[/TD]
[TD]$435.27 [/TD]
[TD]Brianna Ziomek[/TD]
[TD]Amber Eisenbeis[/TD]
[TD]Shante Katinsky[/TD]
[TD] [/TD]
[TD="align: right"]4/2/2013[/TD]
[TD="align: right"]$0[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]April[/TD]
[TD="align: right"]2013[/TD]
[TD]April[/TD]
[TD="align: right"]2013[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col span="3"><col></colgroup>[/TABLE]