Forum Discussion

KyleMB350's avatar
KyleMB350
Frequent Visitor
2 years ago
Solved

Custom Matrix Aggregation for Timesheet Report

I have a timesheet summary view of resource hours by week per project. Hours are logged by day.

  October     
  10/210/910/1610/2310/30 
Resource      200
 Project A2020202020100
 Project B2020202020100

 

I have a requirement to show a utilization view where I divde the weekly hours by 40 to get a utilization percentage to show a resource's utilization among all projects they are on. I can divide the hours worked by 40 to get a correct weekly subtotal, but the issue is that I need to AVERAGE the monthly total and then sum those subtotals to get an overall utilization figure for the resource. See totals below.

 

  October     
  10/210/910/1610/2310/30 
Resource      5 1.0
 Project A0.50.50.50.50.52.5 0.5
 Project B0.50.50.50.50.52.5 0.5

 

Is this possible? Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

4 Replies