Pivot table formula (calculated field) result is incorrect

P

Paul

Underlying data has several attributes plus shares. It also uses a lookup
field to populate a price and date column. Looks like this (not all
attributes listed):

Account Dealer Branch Rep Fund AcctType Shares Price Date

The pivot table essentially summarizes the data using all attributes listed
above except Account (i.e. takes it up one level). Here's the catch. The
pivot table is being used to replicate the results of a program for testing
purposes. The program is rounding at a specific breakpoint - Dealer, Branch,
Rep, Fund, AcctType - meaning that it is summarizing shares at the level
indicated, then multiplying the results for that level by the price for the
corresponding fund and rounding to 2 decimals to obtain a base record. All
further program summarizations represent the addition of one or more of these
base records - ensuring no further rounding occurs.

Pivot table columns are therefore:

Dealer Branch Rep AcctType Fund Price 'Base Record Shares'

where 'Base Record Shares' is the Sum of Shares for that level from the
underlying data.

I have added a Calculated Field as follows:
Based Record Values = ROUND(Shares*Price,2)

Since I was unable to do the price lookup in the formula, I added it as a
column in the underlying data so i could use it in this formula. The formula
is trying to replicate the rounded base value from the program - taking a
share summary and multiplying it by the price and then rounding the result.

Here are some sample values:

Dealer Branch Rep Acct Type Fund Price Base Record Shares Base Record
Value
1234 000 ABCD Rtmt 427 27.42 3,845.569
210,891.00
460 9.78 18,960.833
185,436.95

As you can see, the Base Record Value is twice the value it should be for
fund 427 (3,845.569 x 27.42 = 105,445.50) and 5.61 times the value it should
be for fund 460. This is consistent throughout the pivot table despite the
number of accounts varying from 0 to 5 for any given summarization.

In fact, in the example above, there are three accounts for fund 427 that
should be included in the total (one with zero shares) and if I change the
shares data field from sum to count it only shows 2 accounts and only
includes the value of one. Actual correct value for the first record is:

Shares = 5,727.907
Value = 157,059.20

This behavior is not consistent - some share summarizations are correct.
Anyone have a clue what might be going on here?
 
P

Paul

Update:
After playing with the underlying data to ensure consistency in the values,
I have fixed the Shares summarization problem - this data field now shows the
correct count and sum.

However, my problem remains with the calculated field for Value except that
now the value is 3 times the correct value for fund 427 and 8.411 times the
correct value for fund 460.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top