Need to Add Column to Data in Pivot Table

[Guest]

Hi all,

Hope I'm posting in the correct forum. I'm trying to create a pareto chart
(column and line) using Pivot Tables and I've got it 2/3 done. I'm stuck on
the last part, and am hoping someone can help me or tell me whether or not
it's even possible.

I have one data field called CONDITION. It has two options, PASS or REWORK.
I currently have a stacked column chart to display the count of the values
of the two. What I want to add is another series that is a percentage of the
PASS to the total. In my pivot table, I have three column under data, PASS,
REWORK, Grand Total. I want to know how I can add one more column that would
be PASS/Total (as a %).

I then want to add another axis on the right hand side of the graph that
would be percentages.

If anyone has any suggestions on how to accomplish this, I'd really
appreciate it. If you need additional information, or I butchered an
explanation, let me know.

Thanks,
Jay

 

[Jon Peltier]

The easiest way is by making a regular chart, not a pivot chart. Pivot
charts don't allow inclusion in the chart of data which isn't in the
pivot table. But with a regular chart you could add a column outside the
pivot table which computes the percentages.

Start with a blank cell not touching the pivot table, and create the
chart type you want. In step 2 of the chart wizard, don't use the Data
Range tab, or Excel will turn the chart back into a pivot chart. Use the
Series tab, and define each series' X and Y values and Name here. Make
an additional series for your percentages.

When the series are all plotted, select the percentages series, and on
the Chart menu, use Chart Type to change it to a line type chart. Then
double click on the new line series, and on the Axis tab, select Secondary.

You can actually get this from within the pivot table, though. Using a
simplified data range, with one column categorizing what you're
assessing (called "What") and another column for your "Condition", I set
up the pivot table with "What" in the Row area, "Condition" in the
Column area, and "Count of Condition" and "Count of Condition2" (yes,
the Condition field is used three times) in the Data area. Then I
dragged the Data field button up to the column area. I right clicked on
the Count of Condition2 header, selected Field Settings, clicked on the
Options>> button, and in the Show Data As dropdown I selected % of Row.
Here's my table in all of its glory:

Data Condition
Count of Condition Count of Condition2
What Pass rework Pass rework
a 3 2 60.00% 40.00%
b 2 1 66.67% 33.33%
c 3 100.00% 0.00%
d 1 0.00% 100.00%

You can make a pivot chart of this range. Select the Count of Condition2
- Pass series, and on the Chart menu, use Chart Type to change it to a
line series; select the Count of Condition2 - rework series and press
the F4 key to repeat. Double click the Count of Condition2 - rework
series and on the Patterns tab select None for line and marker to hide
the series. Select the "Legend", then select the Count of Condition2 -
rework legend entry, and press Delete to hide it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______