Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
IntaBruce
Helper II
Helper II

Average of measure in a matrix

Hi,

I have the following Matrix table:

IntaBruce_1-1682873296487.png

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:

IntaBruce_0-1682873898872.png

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

1 ACCEPTED SOLUTION

Thank you Ben,  that does it

IntaBruce_0-1682940126626.png

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!

View solution in original post

9 REPLIES 9
bcdobbs
Super User
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

LinkedIn | Twitter | Blog

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

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:

IntaBruce_2-1682928197857.png

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

 

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

LinkedIn | Twitter | Blog

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

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

IntaBruce_0-1682934140950.png

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

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

LinkedIn | Twitter | Blog

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

@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

LinkedIn | Twitter | Blog

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

Thank you Ben,  that does it

IntaBruce_0-1682940126626.png

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

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:

IntaBruce_0-1682926548539.png

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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