Apple Intelligence is now available on iPhone, iPad, and Mac!

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Calculated field in Pivot table - Numbers

I have a main sheet where all my account transactions are listed. There is a pivot table with the sum of all my spending and earnings. I want to create an expense ratio to understand the data better.


The data shown is not real.


After adding the expense ratio (with the formula Withdrawal amt/Deposit Amt), it should look like


Please guide me on adding a calculated field to a pivot table or the preferred ways to achieve the expense ratio.


Thanks in advance :)

Posted on Apr 3, 2023 8:45 AM

Reply
Question marked as Top-ranking reply

Posted on Apr 3, 2023 10:11 AM

If you really don't like having to do the copy-paste then you can also add extra columns in the data tables, something like this:






The formula in D2, filled down:


=MONTH(A2)


The formula in E2, filled down:


=SUMIF($D,$D2,C)÷SUMIF($D,$D2,B)÷COUNTIF($D,$D2)


The Pivot Table then looks something like this:




I find it easier to just copy-paste, though.


SG

2 replies
Question marked as Top-ranking reply

Apr 3, 2023 10:11 AM in response to Shanthosh05

If you really don't like having to do the copy-paste then you can also add extra columns in the data tables, something like this:






The formula in D2, filled down:


=MONTH(A2)


The formula in E2, filled down:


=SUMIF($D,$D2,C)÷SUMIF($D,$D2,B)÷COUNTIF($D,$D2)


The Pivot Table then looks something like this:




I find it easier to just copy-paste, though.


SG

Apr 3, 2023 9:44 AM in response to Shanthosh05

I don't see a way of doing calculated fields in a Numbers Pivot Table.


One thing you can do, of cours, is copy-paste the cells from the Pivot Table into a regular table and apply a formula there. Not fully automatic, but quite easy, especially after the Pivot Table has done the heavy lifting of calculating subtotals by month.


SG

Calculated field in Pivot table - Numbers

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.