Pivot Chart Total Columns

[Hans]

Hi,

I have a standard pivot table/chart combination displaying project
costing&budget data for several projects. I would like to plot in the chart
the sum of budgets and/or costs over different combinations of projects. But
I only succeed in charting the individual entries per project.

Any advice?

Thanks,

[Jon Peltier]

Hans -

You've discovered that pivot charts aren't very smart. They chart all
the individual data in a pivot chart, and none of the totals or subtotals.

Have you tried grouping the data, or perhaps taking out fields which
subdivide other fields, for example, the quarters in the bad
representation below:

2003 Q1
Q2
Q3
Q4
2004 Q1
Q2
Q3
Q4
2005 Q1
Q2
Q3
Q4


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


Hans wrote:

> Hi,
>
> I have a standard pivot table/chart combination displaying project
> costing&budget data for several projects. I would like to plot in the chart
> the sum of budgets and/or costs over different combinations of projects. But
> I only succeed in charting the individual entries per project.
>
> Any advice?
>
> Thanks,
>

[Hans]

Hi Jon,

Why is it that Pivots seem so limited in their usefullness?

My datasets (Columns) would look like:
Project CostType Month CostAmount Budget

And I would like to be able to make some cross sections of these data
(preferably on the fly in a meeting):
- What is the Total CostAmount for CostType A,B and D in jan, feb, mar for
all projects
- what was total CostAmount for CostType A for projects 1, 3 and 4 together
month by month this year referenced to their total budget
-...

Sigh

"Jon Peltier" wrote:

> Hans -
>
> You've discovered that pivot charts aren't very smart. They chart all
> the individual data in a pivot chart, and none of the totals or subtotals.
>
> Have you tried grouping the data, or perhaps taking out fields which
> subdivide other fields, for example, the quarters in the bad
> representation below:
>
> 2003 Q1
> Q2
> Q3
> Q4
> 2004 Q1
> Q2
> Q3
> Q4
> 2005 Q1
> Q2
> Q3
> Q4
>
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Hans wrote:
>
> > Hi,
> >
> > I have a standard pivot table/chart combination displaying project
> > costing&budget data for several projects. I would like to plot in the chart
> > the sum of budgets and/or costs over different combinations of projects. But
> > I only succeed in charting the individual entries per project.
> >
> > Any advice?
> >
> > Thanks,
> >
>

[Jon Peltier]

I think part of it is how difficult it must be to program the pivoting
within the charts. In order to allow this, Microsoft made some design
decisions, which in turn reduce other aspects of the charts' functionality.

I've written routines that recognize when a pivot table has changes,
then replace all the existing charts in a worksheet with new charts that
reflect the new pivot table structure. I typically use a handful of
regular charts rather than one pivot chart, so I can selectively display
different charts for each value of a major pivot field.

This is not an easy undertaking, but it adds a great deal to a pivot
table's functionality without saddling it with the limitations of a
pivot chart.

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

Hans wrote:
> Hi Jon,
>
> Why is it that Pivots seem so limited in their usefullness?
>
> My datasets (Columns) would look like:
> Project CostType Month CostAmount Budget
>
> And I would like to be able to make some cross sections of these data
> (preferably on the fly in a meeting):
> - What is the Total CostAmount for CostType A,B and D in jan, feb, mar for
> all projects
> - what was total CostAmount for CostType A for projects 1, 3 and 4 together
> month by month this year referenced to their total budget
> -...
>
> Sigh
>
> "Jon Peltier" wrote:
>
>
>>Hans -
>>
>>You've discovered that pivot charts aren't very smart. They chart all
>>the individual data in a pivot chart, and none of the totals or subtotals.
>>
>>Have you tried grouping the data, or perhaps taking out fields which
>>subdivide other fields, for example, the quarters in the bad
>>representation below:
>>
>>2003 Q1
>> Q2
>> Q3
>> Q4
>>2004 Q1
>> Q2
>> Q3
>> Q4
>>2005 Q1
>> Q2
>> Q3
>> Q4
>>
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>Hans wrote:
>>
>>
>>>Hi,
>>>
>>>I have a standard pivot table/chart combination displaying project
>>>costing&budget data for several projects. I would like to plot in the chart
>>>the sum of budgets and/or costs over different combinations of projects. But
>>>I only succeed in charting the individual entries per project.
>>>
>>>Any advice?
>>>
>>>Thanks,
>>>
>>