How to show average productivity

Question

I am trying to figure out how I can get an average of the staff's productivity.

An example of the columns in use is below. I have this as a Data Model as it links to other tables.

I want to be able to use slicers so I can see the results per region, Month, week or staff member (or a combination of the above)

As everyone works a 40 hour week, I used the below formula to show the percentage of productivity per entry, but not sure if I really need to do this.

=IF([@Productive]="Yes",(100/40)*[@[Hours]],0)

Using this on a pivot table I was able to show the productivity of each staff member per week, but this is not really what I am after.

Is there a formula or VBA that I could use to work out the productivity in total and then filter it down accordingly. As this table will continuously grow during the year, I don't think I should be hard coding in weeks or months into the formula.

I tried using SUMIF ( =SUMIF(RawData[Staff Member],A2,RawData[Productive %]) )to get a total of the productivity for each user, and put that into another table but that did not really work as it was giving me 281% etc as it was just a total and would not filter down to the selected month/week/year

I have tried using AVERAGE in a Pivot Table, but that just gave really weird results.

I am really at a loss on how I can do this, if it can be done at all.

Even as an alternative I would be happy to have it show 2 different results in different tables for months or weeks, which I could then filter down to Region/Staff member

I am completely at a loss and would really appreciate some guidance/assistance.

Many thanks

DD