Hi all

Perhaps someone can help me with Pivot Table Calculated Fields.

I have a dataset for month the of August 2019 including the time time taken to complete each unique job and the commencement hour of each job.

In the pivot I have categorised all completed jobs by commencement hour. I have then added an average of completion time in the Values field, this works because Excel is averaging a number.

I also added an average of the total unique jobs that occurred per commencement hour for the month. I can do this by inserting a 1 against each unique job in my dataset and then insert a calculated field in the pivot table as such, =SUM ('unique job count') / 31 days which is correct.

I then thought I could go one step further and add another calculation which also averages by day type. I.e. there were 5 Mondays and 4 Sundays in August. If I then added a filter which only selected Monday in a pivot filter then I would expect the Calculate field to divide by 5 for a Monday and 4 for a Tuesday. This has me stumped though. For instance I added a vlookup in my dataset which looked up the number of Mondays in August, I then added a calculated field as follows = SUM ('unique Job Count') / SUM ('Total Number of Days). However the result is incorrect. Any suggestions?

I know I could easily do this by copying the values from the Pivot chart but then you cannot take advantage of using slicers.

Thanks in advance



Thank you

Quentin