CODEWITHSUNDEEP
google-sheetspivot-tablecalculated-field
Kim Hopkins
Kim Hopkins

Reputation: 85

How to Insert a Calculated ROW Into a Pivot Table Google Sheets

Anyone have an idea? Would love to solve this mystery! I can't believe I can't find this anywhere so must be doing something stupid, but so be it.

Problem: How do you insert a calculated ROW into a pivot table in Google sheets?

In my example, I have a pivot table of financial data across multiple properties. There are rows for Income and Expenses by property (simplified for example), with the properties as columns.

I want to insert ROWS (not columns!) into the pivot table to calculate KPIs like Profit (= Income - Expense). (There are more complicated calculations but simplifying here for sake of example).

I know how to insert a Calculated Field, which provides a COLUMN with the specified formula. However, I can't figure out how to insert a calculated field that produces a ROW with a specified formula.

Example below.

Thank you in advance! Kim

Database:

enter image description here

Pivot Table: enter image description here

In the pivot table example above, I would want to add two calculated rows:

  1. The first would calculate the Net Operating Income (= Income Total - Expense Total)

  2. The second would calculate the Cash Flow (= Net Operating Income - Other Expense Total).

Upvotes: 2

Views: 3079

Answers (2)

Michael Brosnahan
Michael Brosnahan

Reputation: 1

Screen shot showing Calculated Field option in Google Sheets pivot table:

Upvotes: -1

Mike S
Mike S

Reputation: 5

You can use calculated item to create custom rows in the pivot table.

i.e. click on pivot table> go to PivotTable Analyze>Fields, Fields, Items & Sets>Calculated Item

Then create custom row for Net Operating income and in the value something like = 'Late Fees' + 'Rent' - 'Repairs'... etc Then another row for Cash Flow = 'Net Operating Income' - 'Other Expense Total'

Upvotes: -2

Related Questions