Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

About Time Calculation

Hello,   How can I sum the above times. I want to use the results on the chart. The result should be 01:05   Regards,
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Currently, we couldn't add measure with Time or Text format data into value field in some charts like bar chart, line chart and so on. If your data is duration format in Power Query Editor, and data format in report value is Time, you can try sum or sumx to sum your time.

    Measure = SUMX('Table',[Column1])

    Here I suggest you to convert your Time format data to number format like hour, minute or second. Then you can succeed to use it in value field in bar charts.

    In my sample, I convert time to minute.

    MINUTE = 
VAR _HOUR = SUMX('Table',HOUR('Table'[Column1]))*60
VAR _MINUTE = SUMX('Table',MINUTE('Table'[Column1]))
RETURN
_HOUR+_MINUTE

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

Samarth_18's avatar
Samarth_18
Community Champion
4 years ago

Hi Anonymous ,

 

Create a measure with below code:-

 

Measure 2 = 
VAR TotalSeconds =
    SUMX (
        'Minutes_data',
        HOUR ( 'Minutes_data'[Minuts] ) * 3600
            + MINUTE ( 'Minutes_data'[Minuts] ) * 60
    )
VAR Days =
    TRUNC ( TotalSeconds / 3600 / 24 )
VAR Hors =
    TRUNC ( ( TotalSeconds - Days * 3600 * 24 ) / 3600 )
VAR Mins =
    TRUNC ( MOD ( TotalSeconds, 3600 ) / 60 )
RETURN
    IF ( DAYS = 0, "", IF ( DAYS > 1, DAYS & "days ", Days & "day" ) )
        & IF ( Hors < 10, "0" & Hors, Hors ) & ":"
        & IF ( Mins < 10, "0" & Mins, Mins )

 

 

Output:-

 

 

Thanks,

Samarth

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you this worked. But I want to graph these results on a chart. I guess the chart doesn't accept it because it's a measure. How can I fix this problem?

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Anonymous ,In which chart you are using it?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Tahnk you Samarth_18 

      It does not matter. There is also contact information and department information in my table. Using these information, I want to show the person-based measure as a graphic. What I want to do is runtime summation.

       

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        4 years ago

        Anonymous , If you put this measure in value section then I think it will work.