About Time Calculation

[Anonymous]

Hello,

How can I sum the above times. I want to use the results on the chart. The result should be 01:05

Regards,

[Samarth_18]

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]

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]

@Anonymous ,In which chart you are using it?

[Anonymous]

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]

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

[Anonymous]

No, it does not accept it at all. Could it be because the format part is text? It does not appear in a different format option.

[Samarth_18]

@Anonymous is it possible for you to share your PBIX file after removing sensitive data?

[Anonymous]

@Samarth_18 I sent the pbix file with a private message. Thank you.

[v-rzhou-msft · Accepted solution]

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.

[mh2587]

Change data type to duration