Summarize times more than 24 hours

Power BI
1

Hi everyone,

I am are interested in the total of the column #4 in HH.MM.SS format " Actual Handle time multiplied by the # of task received. this result should be 588:49:05. as per managers info. but there are son figures with more that 24 hours and shows an error when I change to time format.
I appreciate if you can guide me on this thanks.
Sandra
APAR.pbix (64.8 KB)
APAR.xlsx (12.1 KB)

2

Answer:

Several things to be addressed here.

  1. Column “Actual Handle Time” is not in proper state, it should be part-of-the-day so for example a duration of 12h would be 0.5 and 15mins would be ~0.0104166… In your model, there is 1 added to the durations of the column
  2. You should use measures in the visuals, not columns

Now, I created a measure that takes care of issue (1) but you have to address this probably at the data source with data cleaning.

Actual HT * NumTasks = 
SUMX(
    'Table 2', 
    [No. of Tasks Received]* ([Actual Handle Time per Function]-1)
)

This returns the expected durations as part-of-day values, so a value of 1.35 would mean 1.35 * 24 * 60 minutes (24h in a day, 60m in an hour)

With dynamic format we can show this part-of-day value as format time; this code goes into the Dynamic Format coding:

VAR _dur = SELECTEDMEASURE() 
VAR _hours  = TRUNC(_dur * 24) 
VAR _mins = TRUNC(_dur*24*60 - _hours*60)
VAR _secs = TRUNC( (_dur*24*60 -_hours*60- _mins) * 60)
RETURN """" & _hours & ":" & FORMAT(_mins,"00") & ":" & FORMAT(_secs,"00") & """"

The descrepancies you see in the last two columns are probably because your existing column was rounding up to some decimal and losing accuracy in multiplications with larger numbers. Same for the total.

image
image797×496 161 KB