Pivot Table Grandtotals. All or nothing?

[Guest]

I asked this question before: when selecting Column Grand Totals is it
possible to restrict them to the columns that make sense? I have columns for
volumes of some 50 products, unit prices for each and sales values. Only the
Sales Value Grand Total has any meaning but I am stuck with a sum of all the
unit prices and all the volumes which are not only meaningless, they are also
confusing.

 

[Roger Govier]

Hi

Regrettably, the answer is no.
How is your report laid out?
You say you have

volumes of some 50 products, unit prices for each and sales values

Do you mean that there are 150 columns across the page or are the
products going down the rows with 3 columns across?
I am just wondering whether you could have your 3 values set out under
each other, so that the summation across the columns would all be
applicable to the same units.

Otherwise, I think you will have to switch off Grand Totals and create
your own Total to the side of the PT using the GetPivotData formulae.

 

[Guest]

I was afraid so, because I looked everywhere for an option to switch off the
Grand Total selectively. The Products are listed on rows, one below the
other, and there are 9 columns 3 years x Quantity x Price x Sales Value.
Grand Totals only make sense for the last column.

 

[Roger Govier]

Hi

Then with the Grand Totals, that would make 12 columns plus the column
with Product making 13 in all
You could just hide columns 11 and 12.

Otherwise, it's a question of creating your own Grand Total, picking up
data from columns 4, 7 and 10.

 

[Guest]

I hid the columns showing grand totals across the rows because they don't
have much use. All I need for my report is a column total for $ sales value
for each of the three years but, unfortunately, Excel also sums up unit
prices and the volumes of 50 different products which are meaningless.

 

[Roger Govier]

Hi

Then just turn off Grand Total by Column, and use a formula below the PT
for your Totals
Since the report length will remain constant for your 50 products, you
could just use something like
=SUM(D554)
in cell D55
Repeat for columns G and J.

 

[Guest]

It seems to me that this should work. Thanks for your help!

 

[Guest]

MIt works great! And why shouldn't it? I got a mental block and was thinking
in terms of PT calculated fields, forgetting that I was on Excel and that a
simple SUM formula would do the trick! Thanks for your patience!

 

[Roger Govier]

No problem, glad to have been of sum help.
Thanks for the feedback letting us know it worked.

 

[Guest]

Just when I thought I was home free, I cycled through a number of customers
(who are selected from the "Page" drop-down list) and a few of them only have
1 or 2 years of activity. It means that, in those cases, columns H,I, J
(where I have =SUM formula) are unpopulated and the Grand Totals appear as
zero! :-((