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

Hi Jake

I agree with you. The PT does appear to be calculating the total AMT2

incorrectly by doing the .31*total Amount, and not summing the

individual amounts where the conditional test would have made

calculation of some of the individual AMT2's zero.

The only way I could get around it, was to not use a calculated field in

the PT, but to have an extra calculated column in the source data.

My test data had columns starting with column A of Name, Date, Amount,

Amount2 in row 1

Rows 2:9 carried values in columns A:C

In D2 I entered the following formula

=IF(SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)>0,

SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)*0.31,0)

/SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2))

and copied down through D3:D9

I then added Amount2 to the Data area, having first deleted the

calculated field AMT2

--

Regards

Roger Govier

I agree with you. The PT does appear to be calculating the total AMT2

incorrectly by doing the .31*total Amount, and not summing the

individual amounts where the conditional test would have made

calculation of some of the individual AMT2's zero.

The only way I could get around it, was to not use a calculated field in

the PT, but to have an extra calculated column in the source data.

My test data had columns starting with column A of Name, Date, Amount,

Amount2 in row 1

Rows 2:9 carried values in columns A:C

In D2 I entered the following formula

=IF(SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)>0,

SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)*0.31,0)

/SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2))

and copied down through D3:D9

I then added Amount2 to the Data area, having first deleted the

calculated field AMT2

--

Regards

Roger Govier

1. Pivot table calculated field - how to make correct Total values?

2. How do I create a calculated field on the Total in a pivot table?

3. Missing Grand Total for a Calculated Field in a Pivot Table

4. Sub Totals as a calculated field in a pivot table

5. Pivot Table Calculated field grand totals

6. Pivot Table Calculated Field (Grand total question)

7. Pivot Table Calculated Field using Grand Total

8. Pivot Table, Calculated Field, Running Total

9. Displaying totals of calculated fields in Pivot tables/charts

10. Pivot tables - possible to show calculated fields at total level only ?

11. How to "blank" pivot table calculated fields if result = 0

12. Problem inserting calculated pivot fields into Pivot Table (2007)

13. Calculated Field and Calculated Item in Pivot Table

14. pivot table formulas for calculated field or calculated item

15. Pivot Table Formulas Calculated Item / Calculated Field

2 post • Page:**1** of **1**