Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 @Anonymous ,
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 @Anonymous ,
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 @Anonymous ,
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
this worked. thank you so much
Thank you so much you are saving my week (at least) - this is perfect!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.