Is there a way how can I sum up these duration in Power BI?

Question

I have Employees and their Overtime hours. There is a cap of 5 hours Max overtime per week, and on basis of that, I submit data to payroll team. However, I am not able to sum this data up, as whenever I try to drag this field into values, it gives me counts/ distinct counts only.

Creating a measure also doesnt work, as it does not allow me to drag that measure in the Values field.

My goal is to display the department wise overtime. But I cannot sum up those hours.

The data type of columns - "Total Overtime", "Max OT Hours", etc is time and format is hh:mm:ss. enter image description here Please help me out.

Answer 1

Create a calculated column as following

Column = CONVERT('Table'[Column1],DOUBLE)

and then you can create a measure like this

Measure = FORMAT(SUM('Table'[Column]),"hh:mm:ss")

If you don't want to create the calculated column and want everything to be done through a single measure, you can use the following

Measure= FORMAT(SUMX(ADDCOLUMNS('Table',"decimalVal",CONVERT('Table'[Column1],DOUBLE)),[decimalVal]),"hh:mm:ss")