I have created a calculated field in my pivot table. It works properly.

However, the column and row totals do not give intended results (sum of

displayed results). Rather they use the same formula on all data in that part

of the table. Here are the details:

Data

Table shows payments by transaction for all customers (field name=AMT).

Customer may have + and - payments on any day. I need to show net payments

per day by customer. I then need to calculate 31% of net payment BUT only if

net >0.

Pivot table: created 2-way table, calculating sum of pmts by customer by

day. works fine

calculated field: =if(AMT>0,AMT*.31,0). works fine in body of table

totals: table does not calculate total of displayed results of formula,

rather reruns formula on underlying data.

example: 2 customers, one has net pmts of 30, formula shows 9.3, second has

net of -20, formula shows 0. I want total of 9.3. table shows total of 3.1

(30-20)*.31.

Any suggestions?

Thanks,

Jake

## Bookmarks