Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

calcaute total hours

Hi,
 
im trying to calcuate total hours of the duration which is HH:MM:SS format in excel, i changedthe type to duration in power query applied following DAX, but couldnt get the result. please help I'm a beginner to PBI
 
Total Hrs = CALCULATE(SUMX(BWay_Data,BWay_Data[Time Duration of the call]),BWay_Data[Lead Status])
  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    3 years ago

    Hi Anonymous ,

     

    It is beacause that in Power Query Editor, there are two data types not currently present in Data or Report View: Date/Time/Timezone and Duration.

    When a column with these data types is loaded into the model and viewed in Data or Report view, a column with a Date/Time/Timezone data type will be converted into a Date/Time, and a column with a Duration data type is converted into a Decimal Number, such as 0.00:00:00 (D.HH:MM:SS).

    You'll likely need to write a measure to format exactly how you want.

    Sample data: 0.18:01:00

    Measure:

    Measure = FORMAT(MAX('Table'[Column1]),"hh:nn:ss")

     

    Or you can use the following formula to create a custom column in Power Query to return the total hours.

    = Duration.TotalHours([Column1])

     

    Best Regards,
    Community Support Team _ xiaosun

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Pls find below

     

     

    • v-xiaosun-msft's avatar
      v-xiaosun-msft
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi Anonymous ,

       

      It is beacause that in Power Query Editor, there are two data types not currently present in Data or Report View: Date/Time/Timezone and Duration.

      When a column with these data types is loaded into the model and viewed in Data or Report view, a column with a Date/Time/Timezone data type will be converted into a Date/Time, and a column with a Duration data type is converted into a Decimal Number, such as 0.00:00:00 (D.HH:MM:SS).

      You'll likely need to write a measure to format exactly how you want.

      Sample data: 0.18:01:00

      Measure:

      Measure = FORMAT(MAX('Table'[Column1]),"hh:nn:ss")

       

      Or you can use the following formula to create a custom column in Power Query to return the total hours.

      = Duration.TotalHours([Column1])

       

      Best Regards,
      Community Support Team _ xiaosun

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.