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.
I have searched the forum and googled but haven't been able to implement those findings to my scenario.
Hope someone can assist me.
member.id | timeEnd | hoursBilled |
1 | 13.4.2022 08:30:00 | 0,5 |
2 | 13.4.2022 10:30:00 | 2,5 |
3 | 13.4.2022 12:30:00 | 4,5 |
1 | 13.4.2022 15:30:00 | 5,0 |
1 | 14.4.2022 12:45:00 | 4,0 |
3 | 14.4.2022 12:30:00 | 3,5 |
2 | 15.4.2022 09:00:00 | 0,5 |
2 | 15.4.2022 12:30:00 | 3,0 |
1 | 15.4.2022 12:30:00 | 4,5 |
Added for further explanation
If we use the little table I provided:
Solved! Go to Solution.
Hi @Icebear ,
First we need a date column, which you can create in PowerQuery.
then please create a new measure.
AVG =
VAR _count_id =
DISTINCTCOUNT ( 'Table'[member.id] )
VAR _avg_day_total =
SUMX (
SUMMARIZE (
'Table',
'Table'[member.id],
"avg", DIVIDE ( SUM ( 'Table'[hoursBilled] ), DISTINCTCOUNT ( 'Table'[Date] ) )
),
[avg]
)
VAR _avg_day_person =
DIVIDE ( _avg_day_total, _count_id )
RETURN
_avg_day_person
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @Icebear ,
Please try this measure.
AVG =
VAR _total = SUM('Table'[hoursBilled])
VAR _count = COUNTROWS(CALENDAR(MIN('Table'[timeEnd]),MAX('Table'[timeEnd])))
VAR _avg = DIVIDE(_total,_count)
RETURN
_avg
result.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Thank you for the reply Gao but this doesn't give the correct result.
If we use the little table I provided:
When I use your measure I don't get 3,89 as a result.
Hope you have time to look at this again.
Many thanks
Hi @Icebear ,
First we need a date column, which you can create in PowerQuery.
then please create a new measure.
AVG =
VAR _count_id =
DISTINCTCOUNT ( 'Table'[member.id] )
VAR _avg_day_total =
SUMX (
SUMMARIZE (
'Table',
'Table'[member.id],
"avg", DIVIDE ( SUM ( 'Table'[hoursBilled] ), DISTINCTCOUNT ( 'Table'[Date] ) )
),
[avg]
)
VAR _avg_day_person =
DIVIDE ( _avg_day_total, _count_id )
RETURN
_avg_day_person
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Thank you so much you are saving my week (at least) - this is perfect!
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 |
---|---|
105 | |
105 | |
88 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
80 | |
72 |