Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
koray
Helper V
Helper V

About Time Calculation

Hello,

 

koray_0-1644327766190.png

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

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])

1.png

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.

1.png

 

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.

View solution in original post

10 REPLIES 10
Samarth_18
Community Champion
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:-

Samarth_18_0-1644329325959.png

 

 

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.
Appreciate your Kudos!!
Connect on Linkedin

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
Community Champion
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.
Appreciate your Kudos!!
Connect on Linkedin

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.

koray_0-1644480034220.png

 

Samarth_18
Community Champion
Community Champion

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

Samarth_18_0-1644480542939.png

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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.

koray_0-1644480735162.png

 

Samarth_18
Community Champion
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.
Appreciate your Kudos!!
Connect on Linkedin

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

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])

1.png

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.

1.png

 

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
Super User
Super User

Change data type to duration 


Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.