Forum Discussion

Fredrik's avatar
Fredrik
Advocate I
8 years ago
Solved

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. Howev...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    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

MFelix's avatar
MFelix
Super User
8 years ago

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's avatar
Fredrik
Advocate I
8 years ago

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