pivot total

[Guest]

I have a pivot where I have dates across the top, and then three values I
have for a field called "Act-Bud", which are Actual, Budget, and a calculated
item, Budget-Actual. Obviously, the calculated item is meant to, within any
month, produce for me the variance between Budget and Actual.

What I'm not getting is a Grand Total that shows each of the three pieces.
In other words, I have each of the three for each month, but then one Grand
Total, which straight sums each row. What I'd like is for each of Actual,
Budet, and Actual-Budget (the calculated item) to Grand Total separately.

Possible?

 

[Jon Quixley]

Boris,

Yes it's possible - in fact it's a bit of a surprise that you don't get
the totals by default which is how it usually happens.
When you have finished compiling the Pivot table, Right click anywhere
over the Pivot table and go to Table Options - This gives you the
options of having totals for rows as well as for columns which should
answer your question

Cheers
Jon

 

[Debra Dalgleish]

If you want the total for Actual, Budget and Budget-Actual, move that
field to the left of the Date field. Then, the pivot table will show the
Actual amounts for each month, and a total Actual.

You could create a copy of the pivot table if you need to see both
totals (month and Act-Bud).

 

[Guest]

I agree with you that it's supposed to happen, but unfortunately even with
options for it, it does not. Any other thoughts on how this could occur?

 

[Guest]

Debra, thanks, but unfortunately what I'm trying to accomplish with the table
is a side by side of each actual/month/variance (actual-month...calculated
item), and then I need the total because it then shows me, at any point of
data, what the year to date is, for all three. But I don't have those totals.

If I make separate tables side by side (thought about that), with the dates
taken out of the second one, and just have it as a total, but I've even
created a new table, and it still won't give me grand totals for all three.

So I am stuck with what sounds like (based on your and Jon's feedback) as a
place I'm not supposed to be, given that the grand totals are supposed to
work for each level of horizontal grouping that has the sum function turned
on.

Any other ideas of how to get this thing to work?

 

[Debra Dalgleish]

The grand totals sum each data field. If you have only one data field
(Amount), there's only one grand total.
Your data is grouped by Month, so the data field also shows a subtotal
per month.

If you want a grand total for budget and a grand total for actual, you
could create separate Budget and Actual fields in your source data.
Add both to the pivot table's data area, and you'll see a grand total
for each.

 

[Guest]

Thanks. If I'm understanding correctly, what I've done incorrectly (for the
purpose of what I want) is that I've used the data field values of Budget and
Actual, and then calculated those using the calculated item function. What
I'm hearing is that I should have two different columns for budget and actual
in the data, and then just leave blank one of them.

Is that right?