How do I get per month average in an Excel Pivot Table

Question

This may sound simple but I can't seem to figure this out. I have a data table that looks like something below. I've created a couple of pivot tables with charts and dropped them on a dashboard with a timeline slicer. Right now it just shows sums, but I want the user to be able to switch between sum and average hours/month.

If I change the total column on the pivot table to average, it shows average hours per employee across all months. How do I set this up so it totals each month and then shows the average of the months?

Employee    |Hours  |Task   |Department |Month
Joe         |20     |A      |East       |Jan, 2018
Joe         |20     |B      |East       |Jan, 2018
Bob         |40     |A      |West       |Jan, 2018
Joe         |20     |A      |East       |Feb, 2018
Joe         |20     |B      |East       |Feb, 2018
Bob         |40     |B      |West       |Feb, 2018
Joe         |20     |A      |East       |Mar, 2018
Joe         |20     |B      |East       |Mar, 2018
Bob         |40     |B      |West       |Mar, 2018

UPDATE Here's what I want the pivot table to look like. I'm showing two different views because as the user changes the month filter it will adjust.

Task    |Jan    |Feb    |Mar    |Sum    |Average
A       |60     |20     |20     |100    |33.33
B       |20     |60     |60     |140    |46.66


Task    |Feb    |Mar    |Sum    |Average
A       |20     |20     |40     |20
B       |60     |60     |120    |60

The issue here really stems from the way the data table is setup. If I collapsed the employee entries down into Task sums (i.e. remove employees) then I could easily get at the average per month. However, I need to keep the data at the employee level.

Answer 1

Without seeing your desired outcome, I will update answer if you provide, here is a way to see both views. You can add the same field twice to the values area and for the first summarize field value by sum and for the other by average

With months as columns (note average is calculated on field record count not distinct count)

To get your average by month count, add your pivottable to the data model, then add the count of month, summarized as distinct count, to the values area, and then add a measure to calculate the sum of hours/distinct count month.