Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Daven
Helper II
Helper II

Average Talktime in HH:MM:SS format

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_IDTeamTalktime_InSecondsTalktime_InHMS
1A50800:08:28
2A47500:07:55
3A30900:05:09
4B77900:12:59
5B27700:04:37

 

 

Would like below results 

TeamAverage Talktime
A0:07:11
B0:08:48

 

Thanks in advance 

 

Daven

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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 :

 

Capture.PNG

 

Capture1.PNG

 

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

View solution in original post

4 REPLIES 4
v-deddai1-msft
Community Support
Community Support

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 :

 

Capture.PNG

 

Capture1.PNG

 

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! 

amitchandak
Super User
Super User

ryan_mayu
Super User
Super User

@Daven 

Please try this

Measure = FORMAT( CALCULATE(AVERAGE(Sheet13[Talktime_InHMS]),ALLEXCEPT(Sheet13,Sheet13[Team])),"HH:MM:SS")

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.