Average issue

[Fredrik]

Hi! 

I am sure this is an easy one but I don't get how to get this right.

Consider the visualisations and the data below. For Month 1, the totalt hours is 12 which equals 4 hrs per person. However the average is calculated as 3 since there are 2 rows for Jack, hence 12/4=3 I guess. For month 2 the average is calculated correctly as 15/3 as there are only one row per person and month. How can I get an average for the total time for month 1, eg. 4 hrs per person?

[MFelix · Accepted solution]

Hi @Fredrik,

You need to make the calculation considering only distinct values something like this:

Average hours =
DIVIDE ( SUM ( Table[Hours] ); DISTINCTCOUNT ( Table[Name] ) )

Based on context it will give you the Hours divided by number of unique users.

Regards,

MFelix

[Fredrik]

Hi MFelix!

Thanks, that works like a charm. I thought it would be easier though. This appears to me as to be a very common scenario.

To get the average to respect the filter selected for week I need to add

Average hours = DIVIDE( DIVIDE ( SUM ( Table1[Hrs]); DISTINCTCOUNT ( Table1[Name] ) ); COUNTROWS(FILTERS('Table1'[Month])))