cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Average of measure in a matrix

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

1 ACCEPTED SOLUTION
Helper II

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!

9 REPLIES 9
Super User

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.

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Helper II

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

Super User

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

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Helper II

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

Super User

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

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Super User

@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``````

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Helper II

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!

Regular Visitor

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

Helper II

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors