Subtotal a calculated field in a pivot table

  • Thread starter Thread starter nekendrick
  • Start date Start date
N

nekendrick

I'm creating a pivot table listing Accounts Receivable transactions by
customer. It includes both payments and invoices in the results.
Unfortunately both payments and invoices are listed as positive
numbers, so I can't just subtotal the "amount" column.

I've created a formula field in the pivot called "calcamt" that
contains an if statement. The if statement examines the transaction
type code and if it indicates a payment, multiplies the amount column
by -1. Otherwise it just returns the amount column as a positive
number.

When I drag the calcamt field into my pivot, I see the results as I
would expect to, payments as negative amounts and invoices as
positives. However, when I now subtotal this by customer, the subtotal
doesn't reflect the payments as negatives. So my detail shows:
Invoice1: $100
Invoice2: $150
Pmt1: -$100
Total: $350

Any ideas?
 
Thanks for the reply. I believe what you are suggesting is what I have
already done. I added a calculated field, with the formula:

=IF(RMDTYPAL=9,CURTRXAM*-1,CURTRXAM)

RMDTYPAL is the "type" field, where 1=invoice and 9=payment.
CURTRXAM is the amount of the transaction.

It calculates the calculated field correctly, I see positives and
negatives in the column, but when it totals that calculated field, it
treats all the results as if they were positive.
 
Back
Top