Lets say i have these PovotTable fields. FieldRow, FieldData1 and FieldData2. I put FieldRow in the Row area of the PivotTable, and so data fields will be summarized for each item of FieldRow. I put FieldData1 and FieldData2 in the Data area, and set them to aggregate by Average. I would like the difference between the average of FieldData1 and FieldData2 (separately for each item in FieldRow). Of course this can be done outside of the PivotTable, but it is hard to maintain since the table may change in size.

I tried creating a CalculatedField with definition "FieldData1-FieldData2". Putting this field in the PivotTable DataField area, the numeric results do not change whatever the aggregation function is set to, Sum, Average, Max, etc., they all give the same numeric result. This is rather confusing. Verifying the numbers, what i found is that the result given in the CalculatedField is in fact the difference in the SUM(FieldData1) - SUM(FieldData2).

So, i went back into the CalculatedField designer and defined the CalculatedField as "AVERAGE(FieldData1)-AVERAGE(FieldData2)". This CalculatedField gives exactly the same result as the previous version (without the AVERAGE function in the definition) - in other words it gives the difference of the SUMS, not the difference of the AVERAGEs. And, this new calculated field also has the same behavior in the PivotTable, that whatever its PivotTable aggregation function is set to, MIN, MAX, SUM, AVERAGE...does not effect the result value.

So, the question is, is there a way to create a PivotTable CalculatedField that is the difference between the AVERAGEs of two other data fields? So far i cannot find a way to accomplish this.

Thanks!