Is this a challenging Pivot Table question?

[ahhua]

Hi,

Pls help. Anyone can solve the following problem:

I would like to create a pivot table with the following format:

Defect%
Order Type Total
A 1.11
B 7

Where Defect% = (Defect Qty / Order Qty) * 100
and Order Qty for Work Order 40037 is 718, Work Order 40038 is 1177 an
Work Order 40039 is 100

With the following data source:

Work Order, Order Type, Order Qty, Defect Code, Defect Qty
40037 A 718 X10 2
40037 A 718 X11 1
40037 A 718 X12 1
40037 A 718 X13 1
40037 A 718 X14 2
40037 A 718 X15 3
40037 A 718 X16 2
40038 A 1177 X13 2
40038 A 1177 X14 1
40038 A 1177 X15 2
40038 A 1177 X16 2
40038 A 1177 X17 2
40039 B 100 X10 2
40039 B 100 X11 1
40039 B 100 X12 1
40039 B 100 X13 2
40039 B 100 X14 1



Thanks

+-------------------------------------------------------------------
|Filename: ExcelForum_PivotTable.JPG
|Download: http://www.excelforum.com/attachment.php?postid=5078
+-------------------------------------------------------------------

 

[Roger Govier]

Hi

The only way I could achieve it was by adding an extra helper column of
data to the table.
In cell F1, I entered OQ2 (Order Quantity 2)
In cell F2
=IF(C2=C1,"",SUMIF($A$2:$A$18,A2,$C$2:$C$18)/COUNTIF($A$2:$A$18,A2))
and copied down to cell F18

Then in the Pivot Table, use
SUM of OQ2 as Data item
SUM of Defect Qty as Data Item

Insert Calculated Field,
PT Wizard>Formulas>Calculated Field>Name % Defect > Formula ='Defect
Qty' / OQ2
Format the % Defect field as Percentage and also drag to the Data area

 

[ahhua]

Hi, Roger Govier

Thank you very much for helping me to solve the problem which I have
struggled for two days to find the solution to fulfill my inhouse
customer requirement. The helper column is really a trick for the
problem and you are my helper expert. It is really wonderful, I have
applied the same concept from your solution to solve my complicated
problem which involves multi levels of data field break down. I am
sorry due to this urgent work, I can only have time now to send my
thank to you.

Because of your help, I didn't make my inhouse customer disappointed
since he was very excited when I first introduced him the wonderful
pivot table and pivot chart.