Average in a Pivot Table

[mikelee101]

Hello,
I have a set of data that looks like this:

Rep Month Day Datapoint Value
Bob 3 1 In Calls 25
Steve 3 1 In Calls 18
Bob 3 1 Out Calls 27
Steve 3 1 Out Calls 32
Bob 3 2 In Calls 19
Steve 3 2 In Calls 36
Bob 3 2 Out Calls 29
Steve 3 2 Out Calls 31

I have a pivot table where I am trying to show Total Calls by
Datapoint (i.e. total In Calls and total Out Calls) per month, Daily
Average per rep by datapoint and Overall Daily Average by Datapoint.
Datapoint is my row field, and Month is my Column Field

The Total and the Daily Average Per Rep are easy (Sum of Value and
Average of Value). The one I'm having trouble with is the Average Per
Day by Datapoint. I'm assuming I'd use a calculated field, but don't
have a ton of experience with them. The formula I'd need, I assume,
would look something like this:

=Sum(Value)/Number of Unique values in Day

Any ideas how I can calculate the number of unique values in Day? Or
is there another way I can accomplish this? Using the sample data
above, I'd be looking for the following results:

Total In Calls 98
Total Out Calls 119
In Calls per Day 49
Out Calls per Day 59.5
In Calls per Rep per Day 24.5
Out Calls per Rep per Day 29.75

If anyone has any suggestions I'd greatly appreciate them.

Thanks,
Mike

 

[Debra Dalgleish]

It's not clear to me exactly what you want to do, but you may need to
add a column to your source data, to count the unique items. There are
instructions and an example here:
http://www.contextures.com/xlPivot07.html#Unique

 

[Herbert Seidenberg]

Add 2 helper columns to source data:
http://www.savefile.com/files/1589347

 

[mikelee101]

Add 2 helper columns to source data:http://www.savefile.com/files/1589347

Thanks for the help. I've already looked at adding a helper column,
but was hoping not to have to do that. The actual data far more
complex than that sample data above (16000+ rows currently, and will
probably be close to 40K by the end of the year, by 27 columns).
I was hoping not to have to add that many formulas, as that would
probably negatively impact calculation time. However, since the data
is being added through VBA, I figure I can put a routine in there
somewhere that will take the previous month's helper column formulas
and replace them with values or something like that.
It just seemed like, if there was a way to do it with a calculated
field out of the existing data, that would be the best way.

Thanks again for the help.

Mike