Calculations in Pivot Tables

S

Su

I've looked in most of the excel groups on google, but have not found
a direct answer to my question, so if this is a repeat, please guide
me to the correct post.

my problem:
my table has 4 columns, dept, costs, amount and Actual/budget

in the pivot table the costs are the rows, actua/budget are the
columns and the amount is the data

I now want to have a variance column that calculates the variance
between actual and budget. I tried the formulas in the pivot table and
it does not let me add a calculated field that does this simple (a-b)
type of calculation. Is there an easy way to do this using pivot table
or if there is another way to do this all suggestions are welcome.

Also I plan to add Dept as page header so that people can select their
dept and see the actuals and budgets. So my formula should be able to
account for this.

Thanks for all your help!

Sue
 
D

Debra Dalgleish

You should be able to create a Calculated item:

Select the Actual/Budget field button
From the PivotTable menu, choose PivotTable>Formulas>Calculated Item
Type a name for the field, e.g. Variance
Enter the formula =Actual-Budget
Click OK
 
S

Su

I tried that option before posting here and what it does is give me a
list of all costs even though the costs dont exist for that dept with
a 0 value. I would then need a way to hide the 0 values.
 
D

Debra Dalgleish

If you have Excel 2002, or later version, you can use a macro similar to
the following, to hide calculated items that are zero:

'=============================
Sub HideZeroCalcItems()
'hide rows that contain zeros for calculated items
'by Debra Dalgleish
Dim r As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim pi2 As PivotItem
Dim pd As Range
Dim str As String
Set pt = Sheets("Pivot").PivotTables(1)
Set df = pt.PivotFields("Units") 'data field
Set pf1 = pt.PivotFields("Item") 'column field
Set pf2 = pt.PivotFields("Rep") 'row field
Set pi = pf1.PivotItems("YearVar") 'calculated item

For Each pi2 In pf2.PivotItems
pi2.Visible = True
Next pi2

i = pf2.PivotItems.Count
For r = i To 1 Step -1
On Error Resume Next
str = Cells(r + 5, 1).Value
Set pd = pt.GetPivotData(df.Value, pf1.Value, _
pi.Value, pf2.Value, str)
If pd.Value = 0 Then
pf2.PivotItems(str).Visible = False
End If
Next r

End Sub
'==============================

For earlier versions, you could use the following code to hide the rows:
'==========================
Sub HideZeroRows()
'hide worksheet rows that contain all zeros
'by John Green
Dim rRow As Range

For Each rRow In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
If Application.Sum(rRow) = 0 Then
rRow.EntireRow.Hidden = True
Else
'DD --I added this to unhide
'any previously hidden rows
rRow.EntireRow.Hidden = False
End If
Next rRow
End Sub
'=========================
 
S

Su

Thanks for your help! That works great!!!


Debra Dalgleish said:
If you have Excel 2002, or later version, you can use a macro similar to
the following, to hide calculated items that are zero:

'=============================
Sub HideZeroCalcItems()
'hide rows that contain zeros for calculated items
'by Debra Dalgleish
Dim r As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim pi2 As PivotItem
Dim pd As Range
Dim str As String
Set pt = Sheets("Pivot").PivotTables(1)
Set df = pt.PivotFields("Units") 'data field
Set pf1 = pt.PivotFields("Item") 'column field
Set pf2 = pt.PivotFields("Rep") 'row field
Set pi = pf1.PivotItems("YearVar") 'calculated item

For Each pi2 In pf2.PivotItems
pi2.Visible = True
Next pi2

i = pf2.PivotItems.Count
For r = i To 1 Step -1
On Error Resume Next
str = Cells(r + 5, 1).Value
Set pd = pt.GetPivotData(df.Value, pf1.Value, _
pi.Value, pf2.Value, str)
If pd.Value = 0 Then
pf2.PivotItems(str).Visible = False
End If
Next r

End Sub
'==============================

For earlier versions, you could use the following code to hide the rows:
'==========================
Sub HideZeroRows()
'hide worksheet rows that contain all zeros
'by John Green
Dim rRow As Range

For Each rRow In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
If Application.Sum(rRow) = 0 Then
rRow.EntireRow.Hidden = True
Else
'DD --I added this to unhide
'any previously hidden rows
rRow.EntireRow.Hidden = False
End If
Next rRow
End Sub
'=========================
Click to expand...
 
S

Su

Debra...after all that, the whole set works great...but now the end
user decided to change the format of the data....

instead of showing both the Actual and Budget data by month, I now
want to only show the actual by month with a grand total. This is easy
enough to do, by just hiding the budget fields for the month.

the problem i'm having, is i also need a second grand total column
displayed for the budget amount. I then have to calculate the variance
between the Actual grand total and the budget grand total. is there a
way to display just the grand totals without actually displaying the
budget data for the months? and also how do I calculate the variance
between the 2 grand totals!

Thanks for your help!

Su
 
D

Debra Dalgleish

You can do those calculations on the worksheet to the right of the pivot
table.

I don't think you'll get the results you want within the pivot table.
 
Top