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?)


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:


Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.