pie chart areas reflect magnitude

[twagner]

I want to show several pie charts all on the same scale.

Example: chart 1 values total to 10, chart 2 values total to 100. So chart
1 is scaled so its area=10, and chart 2 is scaled beside it so its area=100.
That would show not only the values of the parts in each pie chart, but the
comparison in magnitude between charts 1 and 2. In this example, chart 2 is
a much greater value, and therefore bigger pie chart.

How can I do that?

I tried it with bar charts, could not find what I wanted there, either.

 

[Jon Peltier]

At best this will produce a set of cartoons which is semi-qualitative
and non-quantitative.

If you post some typical data, perhaps someone could attempt a better
chart type.

- Jon

 

[twagner]

Here is a typical application with real data.

Consider a system of conveyors to haul dirt out of a mine. The hourly
operating cost of each conveyor is a sum of:
power used
operator labor
maintenance labor
parts consumed

The system in question has 16 seperate conveyors; for simplicity, i will
only include 3 of them here. Costs are per operating hour.

CONVEYOR 1:
power: $343.56
operator: $9.62
maintenance: $0.48
parts: $31.96
total: $385.62

CONVEYOR 2:
power: $1062.04
operator: $9.62
maintenance: $0.48
parts: $40.22
total: $1112.36

CONVEYOR 3:
power: $434.84
operator: $9.62
maintenance: $0.48
parts: $30.28
total: $475.22

The result I am expect would show 3 pie charts, with #1 being the smallest
in total area, #3 being a little larger, and #2 being the largest by far.

You would see that the power is the vast majority of the cost for #2. Power
is large on #1 and #3, but the parts as a percentage show more influence on
#1 and #3 than they do on #2.

Excel will give me the actual percentages of each cost component, but I want
the 3 charts areas to be proportional to their totals. Short of saving all
the graphs as jpegs and manipulating them in photoshop, measuring the
diameters with a ruler and compressing/expanding them, etc.,..... is there a
way to make Excel do that operation for me?

Or think of it this way: a single discrete area of any one chart in any
section of a chart repesents $1. By having the area of #2 shown as much
larger than the other two, it is intuitively apparent that #2 is the largest
cost machine, and power is the largest cost part of that largest machine.

Quantitatve I can get from the numbers; qualitative is what I want to show
in a quick intuitive format to the client.

Thanks in advance for your comments.

 

[Jon Peltier]

Well, there's nothing wrong with a qualitative view that is also
quantitative. I've made a bar chart which is a good representation of
your data:

http://peltiertech.com/images/2009-08/ConveyorBars1.png

If you want to display percentages and totals, you could add data
labels. I've put total dollars on the Total bars, and percentages on the
others in this chart:

http://peltiertech.com/images/2009-08/ConveyorBars2.png

While Conveyors 1 and 3 have a greater influence of parts and operator
costs, power still makes up 89% and 92% of the total, compared to 96%
for Conveyor 2. Not as significant an effect as your description at
first led me to think. It's more a case of all conveyors having small
constant or nearly constant costs plus a huge cost of power. If the
power cost is lower, then the small items contribute a somewhat greater
percentage.

- Jon

 

[twagner]

You are right about the power and its influence on costs. The other machines
in the system show similar results. I was just trying to find a good way to
present that information.

I know from experience with this client that they use pie charts, so I was
trying to give them something in a familiar format. My original question
asked if there was a way in Excel to make the areas of several pie charts
reflect the magnitude of the numbers upon which they are based. Sounds like
the answer is "no."

Perhaps a stacked bar chart would be even more intuitive, as the total can
be easily compared machine to machine. Then the color for any one component
will make its contribution to that total easy to see.

I appreciate your thoughts.

 

[Jon Peltier]

The answer to your original question is actually yes, but it's a pain
and it will not result in a cognitively effective display, no matter how
many pie charts the client is used to seeing.

A stacked bar chart is nearly as ineffective as a pie.

I have actually blogged about this problem:

Simple Bar Chart Beats Complex Multiple Sized Pies -
http://peltiertech.com/WordPress/simple-bar-chart-beats-complex-multiple-sized-pies/

In the article and in the comments, a number of ways are shown to
display your information. I like the clustered bar chart for its
simplicity, or the offset-stacked bar chart (the last chart in my long
comment). In this case, where one factor dwarfs the rest, a stacked or
offset-stacked chart works. If there were several factors of roughly the
same value, then a clustered chart is the way to go.

- Jon