Pivot Table | Calculated Field | Using an Average Rather Than a Sum

[jeffgibson55]

I am building pivot tables off a data table that includes orders for training events ('Order Table'). In this table each row signifies 1 order. Each event has a unique event ID, so in this table there are usually several rows for the same event (1 for each order the event had). For each order there are various columns that contain demographic fields about each order.

The events are broken into various topical groups and my pivot tables are being used to analyze each of these groupings. Within the 'Order Table' I am doing a vlookup to an external table to determine how many events have been held for each topical group being analyzed. So since an event has a row for every order it got, this reference number repeats itself in each row. This is not a problem when I'm pulling in the raw "events held" number because I just use an Average function which essentially removes the impact of the duplicates.

But I also need to do some calculated fields using this average value as the denominator (e.g., orders per event held). But when I do a calculated field it automatically uses the sum value not the average value and I don't see how you can change that.

Does this make sense? Basically I need to do a calculated field within a pivot table that uses the average rather than the sum in performing the calculation.

Any help would be very much appreciated as I seem to run into this situation a lot and in the past I've ended up just doing the calculations outside the pivot table but referencing cells in the pivot table which is very messy.

Thanks,
Jeff

 

[jae113]

If you have the average field in your data source, is that column or row included in the pivot table's data source?

There is an option to average with in the pivot table. In teh Pivot Table Field List on the right, click the drop down for Sum - Select Value Field Settings - change to average

 

[jeffgibson55]

My question is in regard to adding a calculated field where the custom formula would reference an average value.

 

[Tardis]

Hi,

You can set any field to Sum, Count, Average, Max, Min, etc.. using the Value Field Settings. Doing it here does an average of the records you are looking at in the pivot table.

I believe that Calcuated Fields in PivotTables can only do math on the "columns" in the source data. So if your source data has columns Num1, Num2, Num3, you can create a calcualted field called "Avg Num" with the formula "=average('Num1','Num2','Num3')" which provides an average of those numbers. It does show as a "Sum of Avg Num", but each "row" in the pivot table is an average of those three numbers. I don't believe you can "reference" anything in a calculated field that is not a defined "column" in your source data.

Any chance you could provide a simplified screenshot of an example of what you are trying to do (a picture is worth a thosand words).

Thanks,
Byron

 

[jeffgibson55]

Thanks guys, here's a simplified screenshot - I hope it makes sense. I'm not sure of the best way to insert an image here but here's a link to the pic hosted at photobucket:

http://s1140.photobucket.com/albums/n563/quasi55/?action=view&current=ExcelQuestion-UsingAveragesinPivotTableCalculatedFields.jpg

Thanks again for the help!

Jeff

 

[Tardis]

Hi Jeff,

See:

http://www.mrexcel.com/forum/misc.php?do=bbcode#code

For all the BB Codes.

 

[Tardis]

Hi Jeff,

I've re-created your sample data and pivot table. I'm not clear on what your manual formulas are. Please provide.

Thanks,
Byron

 

[jeffgibson55]

Sorry, the manual formulas are:
A: 23,762 / 10 = 2,376
B: 15,505 / 5 = 3,101
C: 12,844 / 4 = 3,211

These are the results that I want to achieve within the pivot table itself somehow.

Thanks!
Jeff

 

[Tardis]

The source of your problem seems to be that Events Held is unique to each Topic (e.g. Topic A has had 10 Events) and it gets SUM'd up. When what you really need is Events Held as a single static number. As far as I know, Calculated Fields are only able to do calculations on the "Sum" of the fields you select. So as long as there are more than 1 row with the number of Events Held, it is going to get summed.

The only workaround I can think of off the top of my head is to clean up the source data so that there is only one row with the Event Held number for each Topic:

Then you can divide the sum of the visits from the sum of the events held correctly

 

[Tardis]

Hey Jeff, Is the snapshot of your data correct? That is are there really only 4 rows for Topic A? If there really are 10 rows (10 Events with Event IDs) for Topic A, then have a column with "1" in it and sum it up. It would look something like this:

Just brainstorming here. Hope it helps.

Cheers,
Byron