Using Calculated Field to Return the Difference Between Two Columns

[jaydee]

Hi guys,

I'm having an issue with getting the pivot table to return the difference between two values because one column uses averages instead of sum.

My pivot table shows:

Order total Prepaid Amt Difference
58.09 1,500 -23,941.91

If I pull up the detail of the prepaid amount 1,500, it is made up of 16 rows, for a total value of 24,00. When I use calculated field of order total minus prepaid amount, it returns -23,941.91, when I really want it to return -1,441.91 (58.09 - 1,500).

Anyone know how I can fix this? I tried order total - (prepaid amount / count(prepaid amount)), but it doesn't work and I am of ideas.

Please see attachment for details.

Thank you in advance.

[mana]

I think it's impossible, then you should insert a new field in data source table.