The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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 |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |