Totals of calculated field in pivot table give incorrect results

[Guest]

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

 

[Roger Govier]

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 D39

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

 

[Guest]

Thanks Roger, looks as if that will work. But I feel as if I'm back in Lotus
1A!

I've never used sumproduct function before. I'll experiment.

Regards,
Jake

 

[Roger Govier]

Hi Jake

Yes, its a real PITA that the PT won't do it correctly, but Sumproduct
isn't that horrendous once you get into it.
Named ranges of course make the whole thing more readable.
Bob Phillips has a good treatise on the subject you might want to read
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

Thanks. I've never spent the time to look at array functions. Hard to teach
an old dog new tricks. The article looks helpful.

Regards,
Jake

 

[Guest]

Got it to work. Thanks. Puzzled over the last operation (dividing by sum of
trues) until I realized only way to ensure multiplicand was either 1 or 0
when using mulitple criterea. Thanks. This will be very useful!

I'm going to create my own "two-way" table: unique extract of customer as
row lables, and relevant dates as column headings. use sumproduct in each
cell with the row and column lables as my criterea. Think that will work?

 

[Roger Govier]

Hi Jake

Glad you got it sorted.
The last part of the formula, was to deal with scenarios (if they
exist), where the same customer crops up with transactions more than
once in each day. Sumproduct will find all of the values belonging to
that customer for that day and produce the correct total for the day,
but if the customer name crops up again further down the list, then the
same total value for that same day would be displayed again.

With that data then being passed to a Pivot table, the Pivot table would
sum the values where customer and day agree, so it would give a value of
2 or 3 times the correct value. By dividing by the sum of true's, then
the value attributed to each customer, when summed through the PT comes
back to the correct value.

Now if you are going to create your own 2 dimensional matrix, with
sumproduct to calculate the total based upon the row and column header,
you won't need that last part of the formula at all, since you will only
be summing by a single occurrence of the customer in the data array, for
any given date not by each occurrence of his name for any given date.

Personally, I prefer the PT approach. It is so much easier to change the
view dragging different fields in and out of different areas of the
table, especially with the use of Page fields to select a given year or
given month.
I seldom tend to use calculated fields inside the PT itself, and I will
certainly avoid them when making comparative type operations as you had
done now I realise that there is a bug there. Let's hope they get it
fixed for Excel 12, which looks to have lots of juicy new features when
it comes to PT's.

By pure chance, I have just answered a post in another thread with a
similar problem