Is this a challenging Pivot Table question?
 

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 and
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 |
+-------------------------------------------------------------------+

--
ahhua
------------------------------------------------------------------------
ahhua's Profile: http://www.excelforum.com/member.php...o&userid=36684
View this thread: http://www.excelforum.com/showthread...hreadid=564198

Is this a challenging Pivot Table question?
 

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 WizardFormulasCalculated FieldName % Defect Formula ='Defect
Qty' / OQ2
Format the % Defect field as Percentage and also drag to the Data area

--
Regards

Roger Govier


"ahhua" wrote in
message ...

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
and
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 |
+-------------------------------------------------------------------+

--
ahhua
------------------------------------------------------------------------
ahhua's Profile:
http://www.excelforum.com/member.php...o&userid=36684
View this thread:
http://www.excelforum.com/showthread...hreadid=564198

Is this a challenging Pivot Table question?
 

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.


--
ahhua
------------------------------------------------------------------------
ahhua's Profile: http://www.excelforum.com/member.php...o&userid=36684
View this thread: http://www.excelforum.com/showthread...hreadid=564198