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

 

[jeffgibson55]

Tardis,

That's too bad that calculated fields can only use the sum, but I agree that that seems to be the case. I'm currently getting my raw granular data from a SQL query, rolling up some of the groupings into reference pivot tables, vlookuping into the reference tables to return some static values into the original data set, and then analyzing the new data. I think I can use your idea by adding an if statement to my lookup formulas to only do the static value lookups if it's the first such value in the data set. I was hoping that calculated fields had some more advanced functionality but I think I can make this work - thanks for the idea!!

Thanks!
Jeff