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

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