Need Help - Converting Seconds to HH:MM:SS and Calculating Average Duration

[manoj_0911]

Need Help - Converting Seconds to HH:MM:SS and Calculating Average Duration

Hi everyone,

I'm seeking assistance with two calculations in Power BI:

1. Converting Seconds to HH:MM:SS Format:

I'm working with a column named `ACW_DURATION` that stores durations in seconds. I've tried the following formula:

```
ACWDurationFormatted = 
FORMAT(
  TIME(
    INT('AGENT_SUBHOUR_V'[ACW_DURATION] / 3600), // Hours
    INT(MOD('AGENT_SUBHOUR_V'[ACW_DURATION] / 60, 60)), // Minutes
    INT(MOD('AGENT_SUBHOUR_V'[ACW_DURATION], 60)) // Seconds
  ),
  "HH:mm:ss"
)
```

However, I'm encountering issues (getting incorrect values or duplicates). Can someone please confirm if this formula is correct and suggest any troubleshooting tips?

2. Calculating Average Duration (ACW Time per Call):

I have two columns: `ACW_DURATION` (seconds) and `ACW_COUNT` (number of calls). I've tried two approaches to calculate the average duration per call:

a. Using SUMX and IF:

```
AverageACWDuration = 
IF(
  ISBLANK(SUM('AGENT_SUBHOUR_V'[ACW_COUNT])),
  BLANK(),
  SUM('AGENT_SUBHOUR_V'[ACW_DURATION]) / SUM('AGENT_SUBHOUR_V'[ACW_COUNT])
)
```

**b. Using DIVIDE:**

```
AverageACWDuration new = 
DIVIDE(
  SUM('AGENT_SUBHOUR_V'[ACW_DURATION]),
  SUM('AGENT_SUBHOUR_V'[ACW_COUNT]),
  0
)
```

Both methods seem to return unexpected results. Could anyone advise on the best approach for calculating the average duration per call and suggest corrections to the formulas if needed?

ALL ACW CALCULATIONS I HAVE , 

ACWDurationFormatted =
FORMAT(
TIME(
INT('AGENT_SUBHOUR_V'[ACW_DURATION] / 3600), // Hours
INT(MOD('AGENT_SUBHOUR_V'[ACW_DURATION] / 60, 60)), // Minutes
INT(MOD('AGENT_SUBHOUR_V'[ACW_DURATION], 60)) // Seconds
),
"HH:mm:ss"
)
-------------------------------------------------------------------------------
AverageACWDuration =
IF(
ISBLANK(SUM('AGENT_SUBHOUR_V'[ACW_COUNT])),
BLANK(),
SUM('AGENT_SUBHOUR_V'[ACW_DURATION]) / SUM('AGENT_SUBHOUR_V'[ACW_COUNT])
)
-------------------------------------------------------------------------------
AverageACWDuration new =
DIVIDE(
SUM('AGENT_SUBHOUR_V'[ACW_DURATION]),
SUM('AGENT_SUBHOUR_V'[ACW_COUNT]),
0
)
-------------------------------------------------------------------------------
AverageACWDurationFormatted new =
IF(
ISBLANK([AverageACWDuration new]),
BLANK(),
FORMAT(
TIME(
INT([AverageACWDuration new] / 3600), // Hours
INT(MOD([AverageACWDuration new] / 60, 60)), // Minutes
INT(MOD([AverageACWDuration new], 60)) // Seconds
),
"HH:mm:ss"
)
)
-------------------------------------------------------------------------------

Thanks in advance for your support!
Manoj Prabhakar

[Joe_Barry · Accepted solution]

Hi @manoj_0911 

I can partly help

Create custom columns.

Hours = INT([Seconds] / 3600)

Minutes = INT(MOD([Seconds], 3600) / 60)

Seconds = MOD([Seconds], 60)

Then create another

DurationFormatted =

FORMAT([Hours], "00") & ":" &

FORMAT([Minutes], "00") & ":" &

FORMAT([Seconds], "00")

Hope this helps

Joe

If you found my answer helpful and it solved your issue, please accept as solution

[manoj_0911]

Hi @Joe_Barry  I appreciate your response, it was really helpful, but I still have some questions. First, my dataset contains 40 durations of this type; given that this is ivr data, can i follow the same approach to convert it to HH:MM:SS? Additionally, for a given average ACW time, I must divide the ACW duration in seconds by the accepted count before converting the result to the hh:mm:ss format. I did this in Tableau; is there a comparable method in Power Bi?" "

AVERAGE ACW TIME = ZN(SUM([ACW DURATION])/SUM([ACCEPTED]))

AVG ACW TIME = IIF([AVERAGE ACW TIME] % 60 == 60,0,[AVERAGE ACW TIME] % 60)// seconds
+ IIF(INT([AVERAGE ACW TIME]/60) %60 == 60, 0, INT([AVERAGE ACW TIME]/60) %60) * 100 //minutes
+ INT([AVERAGE ACW TIME]/3600) * 10000 //hours

"

[Joe_Barry]

Without really knowing the data, I would suggest that you find the average before you convert to hh:mm:ss.

Try and test first to see if you get the result you require