cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

Hello,

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

Regards,

1 ACCEPTED SOLUTION
Community Support

Hi @koray ,

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.

10 REPLIES 10
Community Champion

Hi @koray ,

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

Best Regards,
Samarth

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

Helper V

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?

Community Champion

@koray ,In which chart you are using it?

Best Regards,
Samarth

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

Helper V

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.

Community Champion

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

Best Regards,
Samarth

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

Helper V

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.

Community Champion

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

Best Regards,
Samarth

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

Helper V

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

Community Support

Hi @koray ,

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.

Super User

Change data type to duration

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors