Forum Discussion

Jenna619's avatar
Jenna619
Regular Visitor
3 years ago
Solved

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

  • Jenna619's avatar
    Jenna619
    Regular 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]))