Custom Matrix Aggregation for Timesheet Report

[KyleMB350]

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

		October
		10/2	10/9	10/16	10/23	10/30
Resource							200
	Project A	20	20	20	20	20	100
	Project B	20	20	20	20	20	100

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/2	10/9	10/16	10/23	10/30
Resource							5 1.0
	Project A	0.5	0.5	0.5	0.5	0.5	2.5 0.5
	Project B	0.5	0.5	0.5	0.5	0.5	2.5 0.5

Is this possible? Thanks!

[Anonymous · Accepted solution]

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.

[KyleMB350]

Thank you! This works perfectly!

[Greg_Deckler]

@KyleMB350 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[KyleMB350]

Thanks Greg. This appears to get me halfway as I needed to sum up monthly averages. I watched your videos on this and definitely have uses for this in my other reports. I definitely voted on that idea!