Forum Discussion
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:
- Card 1: Display the SUM of Available Time in HH:MM:SS format.
- Card 2: Display the AVERAGE of Available Time in HH:MM:SS format.
- Line Chart: Display the Available Time trend over time with values in HH:MM:SS format on the Y-axis.
- 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
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.
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.
4 Replies
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.
Total seems to be wrong
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.
manoj_0911 , Make sure you time in second, You can use Dynamic string format to display time is in sec
