cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## How to convert decimal time to hh:mm and summarize that column?

Hi,

I need to display for every person how long she worked each day and I need to display the summarized hours of all persons. It should be displayed in the format hh:mm.

Therefore I use a measure to convert the decimal time [IST] to minutes and another measure to convert from minutes to hh:mm.

minutes = TRUNC([IST])*60+(([IST])-Trunc([IST]))*60

hh:mm = [minutes]/60/24

It looks like this:

I don't really need to display the display the colums Ist and minutes.

I only need to display the column hh:mm and the total sum below.

How could I achieve that?

Many thanks for hints.

1 ACCEPTED SOLUTION
Super User

Then display results in visuals as text (eg display 65.5 mins  =  01:05:30)

Please click the thumbs up if you like me trying to help you and click Accept as Solution if I have fixed you problem.

Thanks.

Total duration in MM:SS =

-- convert decimal duration to hh:mm:ss text
-- eg converts  65.5 to  "01:05:30"   and converts 0 to  00:00

-- Take decimal minutes as input eg 5.5 minutes

VAR DecimalMinutes = AVERAGE(Calls[Conversation duration])

-- Convert decimal minutes to seconds

VAR Duration = DecimalMinutes * 60

-- Calc the days, hours, minutes and seconds  (add days if required

VAR Hours = INT(DIVIDE(Duration,3600,0))

VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

-- Format hours, minutes and seconds to two decimals

VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))

VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))

VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))

RETURN

-- if input is 0 return 00:00.

-- return hours:mins:seconds as text

-- handles any errors(unlikely)

-- if input is greater than 24hrs then the hours will be displayed

IFERROR(

IF (DecimalMinutes>0,

H & ":" & M & ":" & S,

"0:00"),

"Error")

5 REPLIES 5
Super User

Then display results in visuals as text (eg display 65.5 mins  =  01:05:30)

Please click the thumbs up if you like me trying to help you and click Accept as Solution if I have fixed you problem.

Thanks.

Total duration in MM:SS =

-- convert decimal duration to hh:mm:ss text
-- eg converts  65.5 to  "01:05:30"   and converts 0 to  00:00

-- Take decimal minutes as input eg 5.5 minutes

VAR DecimalMinutes = AVERAGE(Calls[Conversation duration])

-- Convert decimal minutes to seconds

VAR Duration = DecimalMinutes * 60

-- Calc the days, hours, minutes and seconds  (add days if required

VAR Hours = INT(DIVIDE(Duration,3600,0))

VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

-- Format hours, minutes and seconds to two decimals

VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))

VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))

VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))

RETURN

-- if input is 0 return 00:00.

-- return hours:mins:seconds as text

-- handles any errors(unlikely)

-- if input is greater than 24hrs then the hours will be displayed

IFERROR(

IF (DecimalMinutes>0,

H & ":" & M & ":" & S,

"0:00"),

"Error")

Super User

You could just do

hh:mm = TIME(0, [IST]*60, 0)

--Nate

I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Super User
Anonymous
Not applicable

Thank you, it works partially. I see the values as expected. How do I get the values summed up?

I could select Do not Summarize, Count or Count distinct from Default Summarization. There are no options to select any further items like Sum.

Community Champion

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors