Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a seconds column for the talktime, I would like to show average talktime for each catgory in the HH:MM:SS format.
Data in the tables
Call_ID | Team | Talktime_InSeconds | Talktime_InHMS |
1 | A | 508 | 00:08:28 |
2 | A | 475 | 00:07:55 |
3 | A | 309 | 00:05:09 |
4 | B | 779 | 00:12:59 |
5 | B | 277 | 00:04:37 |
Would like below results
Team | Average Talktime |
A | 0:07:11 |
B | 0:08:48 |
Thanks in advance
Daven
Solved! Go to Solution.
Hi @Daven ,
Would you please refer to the measure below:
Measure =
VAR _Duration =
AVERAGEX ( 'Table', 'Table'[Talktime_InSeconds] )
VAR Hours =
INT ( _Duration / 3600 )
VAR Minutes =
INT ( MOD ( _Duration - ( Hours * 3600 ), 3600 ) / 60 )
VAR Seconds =
ROUNDUP ( MOD ( MOD ( _Duration - ( Hours * 3600 ), 3600 ), 60 ), 0 )
RETURN
Hours * 10000 + Minutes * 100 + Seconds
Then change the data type to custom and 0:00:00 :
For more details ,please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERNkGZGrCQxIs3eBOT...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @Daven ,
Would you please refer to the measure below:
Measure =
VAR _Duration =
AVERAGEX ( 'Table', 'Table'[Talktime_InSeconds] )
VAR Hours =
INT ( _Duration / 3600 )
VAR Minutes =
INT ( MOD ( _Duration - ( Hours * 3600 ), 3600 ) / 60 )
VAR Seconds =
ROUNDUP ( MOD ( MOD ( _Duration - ( Hours * 3600 ), 3600 ), 60 ), 0 )
RETURN
Hours * 10000 + Minutes * 100 + Seconds
Then change the data type to custom and 0:00:00 :
For more details ,please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERNkGZGrCQxIs3eBOT...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Thanks Dedmon
It works perfectly!
@Daven , Try like New column
time = time(0,0,[Talktime_InSeconds])
or
Please try this
Measure = FORMAT( CALCULATE(AVERAGE(Sheet13[Talktime_InHMS]),ALLEXCEPT(Sheet13,Sheet13[Team])),"HH:MM:SS")
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |