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 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!

[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

--
Regards

Roger Govier


"ahhua" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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.