Calculated field in Pivot table - Numbers

Question

Level 1

4 points

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

Answer 1

Best reply

SGIII

Level 8

35,426 points

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

Reply

Answer 2

SGIII

Level 8

35,426 points

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

Reply

Calculated field in Pivot table - Numbers

Similar questions