Forum Discussion

mouzi4us's avatar
mouzi4us
New Member
9 years ago
Solved

Changing the count to sum in time format

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 e...
  • BhaveshPatel's avatar
    BhaveshPatel
    9 years ago

    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

     

     

BhaveshPatel's avatar
BhaveshPatel
Icon for Super User rankSuper User
9 years ago

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