Help Formatting Time in Power BI Report

[manoj_0911]

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.

[Anonymous]

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.

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

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]

Total seems to be wrong

[Anonymous · Accepted solution]

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 )

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]

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

https://youtu.be/qHALucET7N8