Display of Calculated Items in Excel Pivot Table

Question

I created a PivotTable to present the budget for the year using 2 Categories (A, B) and also create a Calculated Item (= Spending / Budget) showing the percentage of the budget used.

In the first Pivot Table of the image the percentage calculation does all right, but when another subcategory is added makes the sum of all percentages instead of just showing the calculation of single row “A” that would have to be 79% (second PivotTable of the image)

Is there any way that, when the pivot table is collapsed, not show the Summarize Values of all Percentages Values within his Category, and when the PivotTable is expanded, only do the Calculated Item but row level appear as in the 3rd PivotTable of the image?

What settings do you have in Value Field Settings > Show Values As? — Matt
I have the Sum option, but when I choose another setting of the list this apply to all the PivotTable. — Jorge Molano
I can't seem to reproduce the problem you're getting. Do you have a sample dataset? — Matt
How did you create the calculated field from this flat layout? — Matt

OldUgly OldUgly · Accepted Answer · 2016-06-02T02:43:42

Pivot Tables are notorious for not having "that one obvious feature".

In this case, the "aggregate of the calculation" (e.g. sum of %Used) should really be the "calculation of the aggregate" (e.g. %Used of the sums). Unfortunately, there doesn't seem to be a way to coerce this out of the pivot table.

As a fallback position, you can use VBA.

In the VBEditor, double click the worksheet containing the Pivot Table
In the first drop down, select Worksheet.
In the second drop down, select PivotTableUpdate

Insert the following code for the PivotTableUpdate event. It should work cleanly for many kinds of changes - I am sure there will be cases that I haven't dreamed up ...

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim rCell As Range, rCol As Range, rRow As Range
Dim lNewCol As Long, lBudgetCol As Long, lSpendCol As Long, lRow As Long
Dim sFormula As String

' Initial
Application.ScreenUpdating = False

' Clean up remnants of old calculation
For Each rRow In ActiveSheet.UsedRange.Rows
    For Each rCell In rRow.Cells
        If InStr(rCell.Formula, "=") Then
            rCell.Delete shift:=xlShiftToLeft
            GoTo NextCell
        End If
        If rCell.Value = "%Used" Then
            rCell.Columns(1).EntireColumn.Delete shift:=xlShiftToLeft
            GoTo NextCell
        End If
        If rCell.Value = "Budget" Then lBudgetCol = rCell.Column
        If rCell.Value = "Spending" Then lSpendCol = rCell.Column
NextCell:
    Next rCell
Next rRow

lNewCol = ActiveSheet.UsedRange.End(xlDown).End(xlToRight).Column + 1

' Implement new calculation and format
For Each rRow In ActiveSheet.UsedRange.Rows
    lRow = rRow.Rows(1).EntireRow.Row
    ActiveSheet.Cells(lRow, lNewCol - 1).Copy
    ActiveSheet.Cells(lRow, lNewCol).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    If lRow = 4 Then ActiveSheet.Cells(lRow, lNewCol).Value = "%Used"
    If lRow > 4 And IsNumeric(ActiveSheet.Cells(lRow, lNewCol - 1).Value) Then
        sFormula = "=R" & lRow & "C" & lSpendCol & "/R" & lRow & "C" & lBudgetCol
        ActiveSheet.Cells(lRow, lNewCol).FormulaR1C1 = sFormula
        ActiveSheet.Cells(lRow, lNewCol).Style = "Percent"
    End If
Next rRow

' clean up
ActiveSheet.UsedRange.End(xlToLeft).Select
Application.ScreenUpdating = True

End Sub

The formula used to calculate %Used is (for example) =C5/B5 as opposed to =GETPIVOTDATA(...)/GETPIVOTDATA(...) for simplicity.

The following screenshots show results as filters and layout are modified ...

Display of Calculated Items in Excel Pivot Table

1 Answers