0

So I have a bunch of rows in a query table that shows orders from a certain timeframe and whether that order had a "defect" or not. I have started a pivot table on that table to summarize the # of orders and # of those orders that were defective by day (trying to start a p-chart).

Is there any way to create a 4th column in the pivot table that calculates the percent defective (Orders with Defects/Count of Orders) from the two existing columns?

enter image description here

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
JavaStudent345
  • 1
  • [Calculate a percentage for subtotals in a PivotTable](https://support.microsoft.com/en-us/office/calculate-a-percentage-for-subtotals-in-a-pivottable-6aa66bce-11c5-47f7-b6b6-0959b0b13a72) I think you need the option `% Of` Anyways, you can add the calculus in your source data and just take that field into the pivot table – Foxfire And Burns And Burns Jun 29 '22 at 13:03
  • @FoxfireAndBurnsAndBurns All the % Of options only let me choose the Date column as the base. I am unsure how to calculate from the source data as it is only rows by Order #, Date, and a Defective yes/no field. – JavaStudent345 Jun 29 '22 at 13:28
  • Can you add a data example of the source and expected output in your pivot table? – Foxfire And Burns And Burns Jun 29 '22 at 13:30

1 Answers1

0

Sure,

Based on this site, these are the steps:

  • To start, select any cell in the pivot table
  • Next, on the Excel Ribbon, go to the PivotTable Analyze tab
  • In the Calculations group, click Fields, Items, & Sets
  • Then, in the drop-down menu, click the Calculated Field command

Once there give a name to your Calculated Field and set your formula to Orders with defect / orders (tip: select the fields from the list to make sure you get the names right).

One last note: I believe calculated fields perform additions by default.. I noticed your 2nd column is a "count", I believe the calculated field will sum that column (before dividing) instead of counting it.

Andres Silva
  • 874
  • 1
  • 7
  • 26