I have a calculated field in a pivot table returning the running total of my period totals (Ending balance of each period). When my period Field in entirely collapsed, it looks great, but when I expand a period to look at individual transactions the calculated field is on all of those rows as well.

I'm ideally looking to see if anyone has a purely pivot settings solution.
I've tried using VBA to clear the contents when the table is updated but run into error 1004 - use field list option to modify pivot table.
I'm currently making the range text white by finding all the required cells then Rng.font.TintAndShade = 0. I'd also like to keep this WB ".xlsx" format so it's easier for me to send to coworkers.

Pivot Collapsed:

Pivot Expanded:

Calculated field settings:

CodePudding user response：

Future solution thanks to @JosWoolley:

• Enable Power Pivot
• Import table into the Data Model
• Use DAX formulas to generate the expected result