cancel
Showing results for
Did you mean: Frequent Visitor

## Average issue

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? 1 ACCEPTED SOLUTION  Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português 2 REPLIES 2  Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português  Frequent Visitor

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])))   