Changing the count to sum in time format

[mouzi4us]

Hi Friends

I have a problem with time format in PBI , i want to demonstrate the over time in column chart , but instead of suming the values for different people , it counts them !!!

in the edit queries i ve changed the overtime format to TIME , but still doesnt work and also there is no Sum in quick calc too .

 i ll be so thankful if u help me on this

[BhaveshPatel · Accepted solution]

It is relatively hard to do aggregations on TIME FORMATS of the data. For this purpose, I have split the column by " : " Delimiter and

then again split by space demiliter to remove AM. Followed by this, I multiplied the hour column by 60 and summed up hour and minutes column together to get total minutes.

It is then divided by 60 and rounded to 4 decimal places to get the actual overtime in hours.

All I have done this in Query Editor and You can finally see the results in the column chart summed up. 

REFER TO THE SCREENSHOTS ATTACHED FOR THE STEPS.SPLIT BY COLONSPLIT BY SPACECREATE CUSTOM COLUMN