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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
manoj_0911
Post Patron
Post Patron

Help Formatting Time in Power BI Report

Hi everyone,

I'm working on a Power BI report with data from a contact center. The data includes durations in seconds (available, on queue), group names, agent names, transaction dates, and IDs. I've exported the relevant data from my MS SQL Server database to Excel and cleaned it up for this report.

My Goals:

  1. Card 1: Display the SUM of Available Time in HH:MM:SS format.
  2. Card 2: Display the AVERAGE of Available Time in HH:MM:SS format.
  3. Line Chart: Display the Available Time trend over time with values in HH:MM:SS format on the Y-axis.
  4. Detail Table: Show the SUM of Available Time and SUM of On Queue Time for each record, formatted as HH:MM:SS.

Attached Files:

  • Sample Data (Excel)
  • Power BI Desktop file (.pbix)

https://drive.google.com/file/d/1ruGgrxxCdQTAVFvqZhquWJk7ZjXNgrub/view?usp=sharing

 

https://docs.google.com/spreadsheets/d/1d27HTjHokUp8mfzh0JnCWleoySD5Kt1w/edit?usp=sharing&ouid=10765...

 

Request:

I'd greatly appreciate any help in achieving these goals with the correct formatting for available and on queue times. This is an important task for me, and I'm eager to learn the best approach in Power BI.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @manoj_0911 ,

Sorry, there might be something wrong with the DAX code I wrote above, I reworked the DAX code and got new results.

Sum AVAILABLE = 
VAR _SumofAvailable =
    SUM ( Sheet1[AVAILABLE] )
RETURN
    ROUNDDOWN ( _SumofAvailable / 3600, 0 ) & ":"
        & ROUNDDOWN ( MOD ( _SumofAvailable, 3600 ) / 60, 0 ) & ":"
        & MOD ( _SumofAvailable, 60 )

vyilongmsft_1-1717568435742.png

vyilongmsft_0-1717568401292.png

vyilongmsft_2-1717568463559.png

 

 

 

 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @manoj_0911 ,

I've created three new Measures that I think will inspire you.

Sum AVAILABLE =
FORMAT ( SUM ( Sheet1[AVAILABLE] ) / 86400, "HH:mm:ss" )
Sum ON =
FORMAT ( SUM ( Sheet1[ON QUEUE] ) / 86400, "HH:mm:ss" )
Average =
FORMAT ( AVERAGE ( Sheet1[AVAILABLE] ) / 86400, "HH:mm:ss" )

Then you will see things below.

vyilongmsft_0-1717552925017.png

vyilongmsft_1-1717552959158.png

Since you can't put Measure in the Y-axis of a line graph, I think you can put Measure in Tooltip.

vyilongmsft_2-1717553062135.png

 

 

 

 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

manoj_0911_0-1717564750935.png

Total seems to be wrong

Anonymous
Not applicable

Hi @manoj_0911 ,

Sorry, there might be something wrong with the DAX code I wrote above, I reworked the DAX code and got new results.

Sum AVAILABLE = 
VAR _SumofAvailable =
    SUM ( Sheet1[AVAILABLE] )
RETURN
    ROUNDDOWN ( _SumofAvailable / 3600, 0 ) & ":"
        & ROUNDDOWN ( MOD ( _SumofAvailable, 3600 ) / 60, 0 ) & ":"
        & MOD ( _SumofAvailable, 60 )

vyilongmsft_1-1717568435742.png

vyilongmsft_0-1717568401292.png

vyilongmsft_2-1717568463559.png

 

 

 

 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@manoj_0911 , Make sure you time in second, You can use Dynamic string format to display time is in sec

https://youtu.be/qHALucET7N8

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.