Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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êsHi 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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |