Forum Discussion
Need help creating a Measure with AverageIf function
I have 'Roster' with employee data. I have it reported weekly but also have 'Roster' [Month] so I can group by month. I need the average count of FT employees by month.
I got the first part of the equation, CALCULATE(COUNTA('Rosters'[WorkerType]),'DTS_Rosters'[WorkerType] IN { "FTE" })
But I need it to divide by the count of weeks in that month (4/4/5 weeks per month)
I created column 'Rosters'[WeekCountbyMonth] so each line has the 4 or 5 that it needs to divide (or for current week it could be 1-5 depending where in the month we are) by. In excel this would be super easy and I would do averageif equal to month but I can't figure it out in PowerBI. I am still new to PowerBI and it has been a steep learning curve but I can't seem to find a forum post asking for similar.
Current Measure that isn't working but is the closest I have been able to get so far
Avg FT Employees =
CALCULATE(COUNTA('Rosters'[WorkerType]),'Rosters'[WorkerType] IN { "FTE" }) /
AVERAGEX(KEEPFILTERS(VALUES('Rosters'[Month])),CALCULATE(AVERAGE('Rosters'[WeekCountbyMonth])))
I was able to solve my own question. Figured I would post in case in benefits anyone else:
Avg FT Employees =CALCULATE( COUNTA('Rosters'[WorkerType]),'Rosters'[WorkerType] IN { "FTE" }) /DIVIDE(SUM('Rosters'[WeekCountbyMonth]),COUNTA(Rosters[WeekCountbyMonth]))
1 Reply
- Jenna619Regular Visitor
I was able to solve my own question. Figured I would post in case in benefits anyone else:
Avg FT Employees =CALCULATE( COUNTA('Rosters'[WorkerType]),'Rosters'[WorkerType] IN { "FTE" }) /DIVIDE(SUM('Rosters'[WeekCountbyMonth]),COUNTA(Rosters[WeekCountbyMonth]))