HHMMSS Duration Total

[Barrie2020]

I currently have a column titled PlayedTime which has seconds converted to show as HH:MM:SS - this appears to be ok

I have a quick measure titled TotalPlayedTime which uses this formula:  TotalPlayedTime = CALCULATE(SUM('1'[PlayedTime])) 

If you see the total below, obviously the total does not add up correctly, am i doing something wrong here?

[v-jayw-msft]

Hi @Barrie2020 ,

Can't see your sample data.

But you can check the following steps and see if the result achieve your expectation.

1# Refer to the below topic to convert your PlayedTime to hh:mm:ss.

https://community.powerbi.com/t5/Desktop/How-to-convert-seconds-to-hh-mm-ss/td-p/310219.

2# Use your formula above to create a measure.

3# convert the measure to hh:mm:ss format.

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Barrie2020]

Hello

Apologies for the delay

I have redone the formula following the steps in the above link as advised, i now have a Custom Column titled TIME which is a Data Type of TIME 

TotalPlayedTime in the image below is a MEASURE based n this formula: 

TotalPlayedTime = CALCULATE(SUM('1'[Time]))

That gives me the result below

I have tried to create a duration measure based on this which does not work 

Duration = MAX( '1'[TotalPlayedTime] ) - MIN( '1'[TotalPlayedTime] )

I Cannot use a Calculate Sum formula as TotalPlayedTime is a measure and not a column

Any help for the overall total would be welcome

[v-jayw-msft · Accepted solution]

Hi @Barrie2020 ,

Try use SUMX() function instead SUM().

https://docs.microsoft.com/en-us/dax/sumx-function-dax.

sumx(table,[TotalPlayedTime])

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

First of all create a measure with the sum of all seconds:

TotalSecondsPlayedTime = CALCULATE(SUM(Seconds)

Then create this measure:

VAR Your_Time = 

IF([TotalSecondsPlayedTime ] <> BLANK();

VAR Duration = [TotalSecondsPlayedTime ]

VAR hh =

INT ( Duration / 3600)

VAR Minutes =

INT ( MOD( Duration - ( hh * 3600 );3600 ) / 60)

VAR Seconds =

ROUNDUP(MOD ( MOD( Duration - ( hh * 3600 );3600 ); 60 );0)

VAR H =

IF ( LEN ( hh ) = 1;

CONCATENATE ( "0"; hh );

CONCATENATE ( ""; hh )

)

VAR M =

IF (

LEN ( Minutes ) = 1;

CONCATENATE ( "0"; Minutes );

CONCATENATE ( ""; Minutes )

)

VAR S =

IF (

LEN ( Seconds ) = 1;

CONCATENATE ( "0"; Seconds );

CONCATENATE ( ""; Seconds )

)

RETURN

CONCATENATE (

H;

CONCATENATE ( ":"; CONCATENATE ( M; CONCATENATE ( ":"; S ) ) )

); BLANK())

[Mariusz]

Hi @Barrie2020 

Try 

Measure = MAX( '1'[PlayedTime] ) - MIN( '1'[PlayedTime] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn