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. 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?

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

2 Replies

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