Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm looking to make a card that shows the average time an event happens, but am hitting a wall in Power BI.
I have the time an event happened as well as the times it happened in 15-minute increments. I want to show the average time the event seems to occur. I can do this easily in Excel but am having issues in Power BI.
There is a quick measure for weighted averages but it wants to do a count of the times, not an average.
Below is an example of the data I am working with to get this average time.
Time | Events |
4:00 AM | 3 |
4:15 AM | 5 |
4:30 AM | 7 |
4:45 AM | 4 |
5:00 AM | 2 |
5:15 AM | 5 |
5:30 AM | 4 |
5:45 AM | 5 |
6:00 AM | 1 |
6:15 AM | 1 |
6:30 AM | 2 |
6:45 AM | 3 |
7:00 AM | 3 |
7:30 AM | 1 |
7:45 AM | 1 |
8:15 AM | 1 |
Thanks in advance for any advice.
Solved! Go to Solution.
See if this works for you. We convert the time to the number of minutes from midnight, then calculate a weighted average.
Calculated Column
Time from Midnight = DATEDIFF(TODAY(), TODAY() & " " & Table2[Time], MINUTE)
Using the TIME() function with only the minutes value does all of the heavy lifting of converting number datatype to time datatype.
Measure
Average Time of Occurrence = TIME ( 0,
DIVIDE ( SUMX ( Table2, [Time from Midnight] * [Events] ), SUM ( Table2[Events] ), 0),
0 )
Also be sure to format the measure with the desired time format
Hope this helps
David
See if this works for you. We convert the time to the number of minutes from midnight, then calculate a weighted average.
Calculated Column
Time from Midnight = DATEDIFF(TODAY(), TODAY() & " " & Table2[Time], MINUTE)
Using the TIME() function with only the minutes value does all of the heavy lifting of converting number datatype to time datatype.
Measure
Average Time of Occurrence = TIME ( 0,
DIVIDE ( SUMX ( Table2, [Time from Midnight] * [Events] ), SUM ( Table2[Events] ), 0),
0 )
Also be sure to format the measure with the desired time format
Hope this helps
David
This is great! Thanks, David!
I'm getting a slight difference between Excel and Power BI. With PBI I get 5:22 and Excel I get 5:25 but I think I'm on the right track. I'll play around with it to see if I can figure out the difference. Might be that the PBI calc is a bit more precise cause when I look at the 4am to 5am example you provided I get the same as you did. I'd prefer more precision anyway.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |