Forum Discussion

Barrie2020's avatar
Barrie2020
Frequent Visitor
6 years ago
Solved

HHMMSS Duration Total

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?

 

 

 

Need Help

5 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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


     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    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())
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Barrie2020
      Frequent Visitor
      6 years ago

      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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.