Can I get a PivotTable calculated field that is the difference of two averages?

TomCon

Active Member
Joined
Mar 31, 2011
Messages
270
Office Version
365
Platform
Windows, Mobile
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!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,102
Messages
5,509,246
Members
408,718
Latest member
Bea2136

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top