Four Week Average - 'by person' not by 'per task and per person'

[PaulTrower]

Hi, I'm stuck knowing how to tackle this.

I've got some data that relates to the tasks that people are allocated to each week.

People are allocated to one or more tasks.

The sum of their tasks is their utilisation.

I would like to get the average of their four week utilisation.

To explain I have pasted a copy of some of the data.

The table in green is the 'raw' data - this shows who is working on what four each of the next four weeks. The 'Average in Power BI' column is what Power BI is showing as the average. I can understand why it is arriving at the answer it is (it is dividing the total by the number of values) - but that isn't what I want.

What I would like to display is what is in the blue table - this sums the values to get each weeks utilisation (this I can get in Power BI), but I can't then get an average to display the total divided by four (the number of weeks).

1/ I hope this makes sense, if not then please post and I can explain further.

2/ I'm assuming that I'm going to have to create a 'measure' but I wouldn't know where to do that (on the table that has the values I assume) or what the DAX would be - can you help?

Thanks.

[PaulTrower]

Hi thanks, I've taken your idea and used the below - thanks for nudging me in the right direction.

Weekly Utilisation = DIVIDE(SUM('Resource Data'[FTE]),DISTINCTCOUNT('Date Table'[Week Commencing]))

[v-rzhou-msft · Accepted solution]

Hi @PaulTrower ,

I think you can try measure as below.

Four Week Average = 
DIVIDE(SUM('Table'[Week 1])+SUM('Table'[Week 2])+SUM('Table'[Week 3])+SUM('Table'[Week 4]),4)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.