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[ at ]gmail.com wrote:
[Quoted Text] > 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?
|