Converting seconds into aggregated HH:MM:SS in a Matrix

[EBI621]

I have a data set with the case number processed by each associate and the time spent in sec. The report has a date range slicer to control which date ranges we want to look at the efforts on.  

Sample dataset:

Date	Associate Name	Cases Done	Effort (seconds)
1/1/19	John Smith	2	100
1/2/19	John Smith	2	150
1/1/19	Jane Doe	1	50
1/3/19	John Smith	4	200

The Matrix I need to build will summarize the average effort each associate spends on a case per day and converted to HH:MM:SS format. The Associate Name field is being used under columns for the Matrix

Matrix:

Date	John Smith	Jane Doe	Total
1/1/19	0:00:50	0:00:50	0:01:40
1/2/19	0:01:15	0	0:01:15
1/3/19	0:00:50	0	0:00:50

I've tried some of the solutions posted in other threads to format the time in HH:MM:SS but those end up displaying all the rows in the Matrix rather than the dates selected in the slicer so I believe those work if the values are displayed as a column rather than aggregated. Is it possible to create a measure that can retain the calculations but simply display the values in a duration format?

[Anonymous]

Hi @EBI621 ,

Check this vedio.

https://www.youtube.com/watch?v=V6GW6Z0-pFo 

Best Regards,

Jay

[MFelix · Accepted solution]

Hi @EBI621 ,

Create the following two measures:

Time Value = 
IF (
    HASONEVALUE ( 'Table'[Associate Name] );
    SUM ( 'Table'[Effort (seconds)] ) / SUM ( 'Table'[Cases Done] );
    SUMX (
        VALUES ( 'Table'[Associate Name] );
        SUM ( 'Table'[Effort (seconds)] ) / SUM ( 'Table'[Cases Done] )
    )
)


Duration Selected Period = 
// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = [Time Value] * 60
// There are 3,600 seconds in an hour
VAR Hours =
    INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
    INT ( MOD( Duration - ( Hours * 3600 );3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds =
    ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 );3600 ); 60 );0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF ( LEN ( Hours ) = 1; 
        CONCATENATE ( ""; Hours );
        CONCATENATE ( ""; Hours )
      )
// Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1;
        CONCATENATE ( "0"; Minutes );
        CONCATENATE ( ""; Minutes )
    )
// Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1;
        CONCATENATE ( "0"; Seconds );
        CONCATENATE ( ""; Seconds )
    )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
IF(Duration = BLANK();BLANK();

    CONCATENATE (
        H;
        //CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )        
        CONCATENATE ( "h"; CONCATENATE(M;"m" ) )
    ))

Result below and in attach file.