Excel Pivot Table - Average per day

Question

I have a table of data

Date        Item    Quantity   Operator
01/01/2014  Item1   3          John
02/01/2014  Item1   7          Bob
02/01/2014  Item2   4          John
03/01/2014  Item1   2          Bob
07/01/2014  Item2   3          John

I have made the table into a pivot table that look like this where Operator is a filter

Operator    (All)

Row Labels   Total
Item1        12
Item2        7
Grand Total  19

I want to add another column for the average for each item per day but needs to be able to filter by Operator.

For example over the 7 day date range, John sells 3 of Item1 and 7 of Item2. This means John sells an average of 0.43 Item1 per day and 1 Item2 per day. (Is this correct?)

Answer 1

I would resolve this using the Power Pivot Add-In. You can add calculations for average per item per day, e.g.

Days in Period:=CALCULATE( ( 1 * ( LASTDATE (Table1[Date]) - FIRSTDATE(Table1[Date]) ) ) + 1 , all ( Table1 ) )

Average Per Day:=SUM([Quantity]) / [Days in Period]

I've built a prototype which you can view or download - its: "Power Pivot demo - Average per days in range.xlsx" in my One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398