J
Jason McDermott
I thought this would be of use to you all.
I had asked last week how to do a combined average in a Pivot Table/Chart,
and now I know.
If you pick "Average" as the summarization of your source data, the Pivot
Table averages each category, then stacks those categories vertically in the
chart. This is useless, since an average should be the total sum divided by
the total count, not a sum of all the averages.
It turns out the Grand Total Column in the Pivot Table did this properly,
but the question was how to plot the grand total column.
ANSWER: You can hide the fields (the categories or coulmn headers) by
double-clicking on them. If you hide them all only the grand total is left,
essentially. Now, my data happened to have a category that had only one data
point, so its grand total was equivalent to the overall GT, and I think
that's why it worked. But you could make or use a similar field to get the
same results.
(There is a "hide detail" option in the Chart, but it doesn't work
completely.)
-Jason
=======================
Original message
=======================
Pivot Chart--I want total average, not sum of averages
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.)
I had asked last week how to do a combined average in a Pivot Table/Chart,
and now I know.
If you pick "Average" as the summarization of your source data, the Pivot
Table averages each category, then stacks those categories vertically in the
chart. This is useless, since an average should be the total sum divided by
the total count, not a sum of all the averages.
It turns out the Grand Total Column in the Pivot Table did this properly,
but the question was how to plot the grand total column.
ANSWER: You can hide the fields (the categories or coulmn headers) by
double-clicking on them. If you hide them all only the grand total is left,
essentially. Now, my data happened to have a category that had only one data
point, so its grand total was equivalent to the overall GT, and I think
that's why it worked. But you could make or use a similar field to get the
same results.
(There is a "hide detail" option in the Chart, but it doesn't work
completely.)
-Jason
=======================
Original message
=======================
Pivot Chart--I want total average, not sum of averages
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.)