Custom Matrix Aggregation for Timesheet Report

Hi KyleMB350 ,

I suggest you to create a Calendar table to help your calculation.

Calendar = 
ADDCOLUMNS (
    CALENDAR (
        EOMONTH ( MIN ( 'Table'[Date] ), -1 ) + 1,
        EOMONTH ( MAX ( 'Table'[Date] ), 0 )
    ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "MMMM" ),
    "WeekStart",[Date] - WEEKDAY([Date],2) + 1
)

Data model:

Measure:

Measure = 
VAR _SUM =
    CALCULATE ( SUM ( 'Table'[Hour] ) ) / 40
VAR _AVG =
    SUMX (
        VALUES ( 'Table'[Resource] ),
        AVERAGEX (
            SUMMARIZE (
                'Table',
                'Table'[Resource],
                'Calendar'[WeekStart],
                "SUM", CALCULATE ( SUM ( 'Table'[Hour] ) ) / 40
            ),
            [SUM]
        )
    )
RETURN
    IF ( HASONEVALUE ( 'Calendar'[WeekStart] ), _SUM, _AVG )

Result is as below.

Best Regards,
Rico Zhou

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