Create a matrix in hh:mm format

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" )
    )

 

Hi  Anonymous ,

 

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