Pivot Chart--I want total average, not sum of averages

[Jason McDermott]

I've scoured the internet for an answer to this and still no luck.
I want my pivot chart to display the overall average labor time (y-axis) for
every order (x-axis) that ALL customers have placed. If I tell Excel to plot
average labor, it averages the orders for each customer and then stacks those
averages, essentially summing the averages. This is useless.
Here's the kicker: when I look at the pivot TABLE, the grand total column is
doing it the right way! It sums all data and takes a count of all data and
divides. It knows a grand total column that is the sum of averages is
useless. Is there a way to plot the grand total column (which should be the
default for averages anyway)?
(Yes, I know I could copy the GT column into another sheet and use a regular
chart. That's what I'll probably end up doing.)

 

[Rob Hick]

I've scoured the internet for an answer to this and still no luck.
I want my pivot chart to display the overall average labor time (y-axis) for
every order (x-axis) that ALL customers have placed. If I tell Excel to plot
average labor, it averages the orders for each customer and then stacks those
averages, essentially summing the averages. This is useless.
Here's the kicker: when I look at the pivot TABLE, the grand total column is
doing it the right way! It sums all data and takes a count of all data and
divides. It knows a grand total column that is the sum of averages is
useless. Is there a way to plot the grand total column (which should be the
default for averages anyway)?
(Yes, I know I could copy the GT column into another sheet and use a regular
chart. That's what I'll probably end up doing.)

Pivotcharts are evil for a number of reasons - this being one of them.
I generally avoid them and use normal charts off the back of the pivot
tables. To do this you need to avoid certain doing certain things and
compile your charts in a certain way. There are various postings about
how to do it but in summary:

- Draw a blank chart by selecting an empty cell away from the pivot
table; clikc the insert chart button and then immediately click finish.
- The most secure way is then to add in the data series manually by
using the 'source data' dialog.
- You can drag and drop the series you want from the pivottable BUT you
must be careful not to select the header row, if you do it will
recognise the pivot table and draw a pivotchart. So only select the
data and then name the series yourself.
- If you find that the chart suddenly turns in to a pivotchart, undo
what you did and do it a different way - once the chart is built it
won't suddenly change; it's only the initial setup that is the tricky
thing.

 

[Jason McDermott]

Thanks, Rob--I guess
Not really the answer I wanted, but at least I know I'm not alone.

 

[Rob Hick]

Thanks, Rob--I guess
Not really the answer I wanted, but at least I know I'm not alone.

when you're working with PivotCharts, you rarely get what you want!
They are probably the most inflexible part of excel, certainly that
I've come across. As I say - don't use them, then you won't ever be
disppointed.