Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table question: How to display total and percent for data simultaneouly | Excel Discussion (Misc queries) | |||
Pivot Table Question | Excel Discussion (Misc queries) | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
Jon Peltier - Pivot Table Result for yesterday's "Complex Chart" Question | Charts and Charting in Excel |