Subtotal a calculated field in a pivot table

[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?

 

[roy.patrice]

I've got a crazy idea. Why not add a calculated field that's Pmt * -1
and add that to the pivot table instead. I think it would work. There's
a good example of what you can do on pivot tables and calculated fields
on http://youlearnexcel.com/pivot.htm#exltcdchampc

Hope this helps.

Pat

 

[nekendrick]

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.