Average of measure in a matrix

[IntaBruce]

Hi,

I have the following Matrix table:

The Utilisation column is a measure calculated as Hours worked divided by Available hours

Available hours is also a measure, calculated as the count of days available in the period (working days minus holidays)

Why is the Department level number of Working Days and Available hours not equal to the sum of the staff members in the dept?

This is causing the huge utilisation percentage which I really want to be the average for the dept.

In case it helps, here's my data model:

And my measures:

Working Days = calculate(count('Calendar'[Date]), 'Calendar'[Is a Working Day]) - count(Leave[date])

Working Hours = [Working Days] * 7.5

Thank you for your help

[bcdobbs]

Power BI evaluates totals as being the absence of a filter rather than automatically summing the rows beneath. Often one of it's most annoying features.

If you look at your model nothing filters Calendar so

calculate(
   count('Calendar'[Date]), 
   'Calendar'[Is a Working Day]
)

Is unaffected by selected staff members.

count( Leave[date] )

however responds to any staff filter. With single member of staff selected it returns their number of leave days in the selected date range. At the department level it returns the number of leave days for the whole department.

To avoid this you need to iterate over your staff table and add up the values:

VAR VisibleDates = 
	CALCULATE (
		COUNTROWS ( 'Calendar' ),
		'Calendar'[Is a Working Day]
		)
		
VAR Result = 
	SUMX (
		Staff,
		VisibleDates - COUNTROWS ( Leave )
	)
	
RETURN Result

Here it takes each visible row in Staff and applies the same caclulation as you had adding up the results.

[IntaBruce]

Hi Ben, Thank you for replying.
I tried your solution but it still didn't give my desired result.  Here is the table with your new measure for Working Days:

As with Daniel's solution I no no longer have any data on rows where staff have no leave days booked

[bcdobbs]

Try setting the relationship between staff and leave as single direction. I think that is causing the problem.

If that doesn't work are you able to provide a demo pbix file so I can recreate the issue?

Thanks

Ben

[IntaBruce]

Hi Ben, It's certainly better, but still not quite right.

It is only totalling the days for the staff with no leave.

How do I shre the Pbix file?  I cant seem to attach it here.

Thanks,  Bruce

[bcdobbs]

Generally have to upload it to one drive and share a link. Or send me a private message and I'll share email address.

[bcdobbs]

@IntaBruce it was a mistake on my part.

SUMX iterates over each row in the Staff table but doesn't force a context transition to take the row and put it into the filter context to filter Leave.

This should sort it:

Working Days =

VAR VisibleDates =
    CALCULATE (
        COUNTROWS ( 'Calendar' ), 
        'Calendar'[Is a Working Day]
        ) 

VAR Result = 
     SUMX (
            VALUES ( Staff[id] ),
            VisibleDates - CALCULATE ( COUNTROWS ( Leave ) )
     )

Return Result

[IntaBruce · Accepted solution]

Thank you Ben,  that does it

Thank you so much.  Now I need to study your DAX and try to understand exactly how it does what it does.  Then apply it to my live report which has a few more variables like Working Hours is Available days less holidays plus approved overtime.  But I feel I'm 90% of the way there and my DAX is slowly improving!

[danielbastidas]

Hello @IntaBruce ! 

Sometimes this happens beacuse the filter context of the measure its not the same you get in the visual, to fix this you can try the following DAX:

1. Create another measure for working days

Working Days fix = SUMX(VALUES('Staff'[dept]), [Working Days])

2. Create another measure for working hours

Working Hours fix = SUMX(VALUES('Staff'[dept]), [Working Hours])

3. Use the new measures in the matrix visual

I hope this fixes it! Let me know if it worked!

If you found this reply helpful, consider to accept it as a solution, this way it will be more helpful for other fellow users

[IntaBruce]

Hi Daniel, thank you for replying.  I have tried your solution but still don't get my desired result.  Here is the table with your fixed measures:

I get the same as before but now the staff with no leave days have blank rows