Create a matrix in hh:mm format

[dandreev]

Hi,
I have a table with 3 columns: Year Month, Specialty, Duration (in seconds)

YearMonth	Specialty	Duration
11/1/2023 0:00	AA	2820
11/1/2023 0:00	AA	3960
11/1/2023 0:00	AA	4200
11/1/2023 0:00	AA	4620
12/1/2023 0:00	AA	1680
12/1/2023 0:00	AA	3420
12/1/2023 0:00	AA	3600
12/1/2023 0:00	AA	5460
11/1/2023 0:00	BB	480
11/1/2023 0:00	BB	660
11/1/2023 0:00	BB	720
11/1/2023 0:00	BB	780
11/1/2023 0:00	BB	960
11/1/2023 0:00	CC	1020
11/1/2023 0:00	CC	1080
11/1/2023 0:00	CC	1140
11/1/2023 0:00	CC	1200
11/1/2023 0:00	CC	1260
11/1/2023 0:00	CC	1320
11/1/2023 0:00	CC	1500
11/1/2023 0:00	CC	1680
11/1/2023 0:00	CC	1860

I am trying to to create a matrix like that with values in hh:mm format

Specialty	Nov 2023	Dec 2023	Avg duration	Month   vs Avg Var	Total
AA	15600	14160	14880	-720	26760
BB	3600	0	3600	-3600	3600
CC	12060	10000	11030	-1030	22060

Thank you in advance!

[jdbuchanan71 · Accepted solution]

Here is an example of a duration measure.

The first measure is just the sum of seconds.

Duration Seconds = SUM ( YourTable[Duration] )

The second measure uses the first to calcuation the time and format it.

Formatted Duration = 
VAR _Seconds = [Duration Seconds]
VAR _Minutes = INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds = MOD ( _Seconds, 60 )
VAR _Hours = INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes = MOD ( _Minutes, 60 )

RETURN
    IF (
        NOT ISBLANK ( _Seconds ),
            FORMAT ( _Hours, "00" ) & ":" & 
            FORMAT ( _RemainingMinutes, "00" ) & ":" & 
            FORMAT ( _RemainingSeconds, "00" )
    )

[Anonymous · Accepted solution]

Hi  @dandreev ,

Here are the steps you can follow：

1. Create calculated column.

Month_Year =
FORMAT('Table'[YearMonth],"mmm")&" "&YEAR('Table'[YearMonth])

2. Create calculated table.

Test =
SUMMARIZE('Table','Table'[Specialty],'Table'[Month_Year],
"Duration",SUMX(FILTER(ALL('Table'),'Table'[Specialty]=EARLIER('Table'[Specialty])&&'Table'[Month_Year]=EARLIER('Table'[Month_Year])),[Duration]))

Table 2 =
var _table1=
SUMMARIZE(
    'Test','Test'[Specialty],
    "Month_Year","Avg duration",
    "Duration",AVERAGEX(FILTER(ALL('Test'),'Test'[Specialty]=EARLIER('Test'[Specialty])),[Duration]))
var _table2=
SUMMARIZE(
    'Test','Test'[Specialty],
    "Month_Year","Month vs Avg Var",
    "Duration",
    SUMX(FILTER(ALL('Test'),'Test'[Specialty]=EARLIER('Test'[Specialty])&&
    'Test'[Month_Year]=
    MAXX(FILTER(ALL('Table'),'Table'[YearMonth]=MAX('Table'[YearMonth])),[Month_Year])),[Duration])
    -
    AVERAGEX(FILTER(ALL('Test'),'Test'[Specialty]=EARLIER('Test'[Specialty])),[Duration]))
RETURN
UNION(
    'Test',_table1,_table2
)

As far as I know, Power BI's default sorting is based on alphabetical order, so it's not the effect you're looking for, we need to go and create a new table to do the sorting

Sort_Table =
var _table1=
 DISTINCT('Table 2'[Month_Year])
return
ADDCOLUMNS(
    _table1,"Index",
    SWITCH(
    TRUE(),
    [Month_Year]="Nov 2023",1,
    [Month_Year]="Dec 2023",2,
    [Month_Year]="Avg duration",3,
    [Month_Year]="Month vs Avg Var",4))

3. Select [Month_Year] – Column tools – Sort by column – [lndex].

4. Connecting tables to each other.

5. Result:

Best Regards,

Liu Yang

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

[dandreev]

@Anonymous I can't thank you enough! I was breaking my head how to do it.

[jdbuchanan71 · Accepted solution]

Here is an example of a duration measure.

The first measure is just the sum of seconds.

Duration Seconds = SUM ( YourTable[Duration] )

The second measure uses the first to calcuation the time and format it.

Formatted Duration = 
VAR _Seconds = [Duration Seconds]
VAR _Minutes = INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds = MOD ( _Seconds, 60 )
VAR _Hours = INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes = MOD ( _Minutes, 60 )

RETURN
    IF (
        NOT ISBLANK ( _Seconds ),
            FORMAT ( _Hours, "00" ) & ":" & 
            FORMAT ( _RemainingMinutes, "00" ) & ":" & 
            FORMAT ( _RemainingSeconds, "00" )
    )

[Kinethik]

Thanks so much jdbuchanan71, this helped me out greatly!

[dandreev]

@jdbuchanan71  Your solution works really good. I think it is very clever.
Thank you, very much!
I found an article that is explaining some other points of time converting.
https://www.mssqltips.com/sqlservertip/7061/calculate-date-difference-power-bi-dax-datediff/