cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fredrik
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
MFelix
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.