Forum Discussion

agu10's avatar
agu10
New Member
3 years ago

Average Weekly Attendance based upon working days

Hi,

I am trying to get an average weekly attendance based upon the number of working days in that week instead of default 5, but not getting the solution to dynamically divide to calculate average. 

I have 2 tables - 

Table 1 - Attendance Table, where I have a date, employeeId (if emplyee came), weeknum (calendar weekNum calculated from date).

Table 2 - Week WorkingDays table, where I simply have weekNum and working days in that week.

 

Now, if in week 1, count of employeeid is 500 and week1 has 5 working days, my week1 average should be 500/5 = 100

and if in week 2, count of employeeid  is 450 and week2 has 4 working days, my week2 average should be 450/4 =  112.5

I am calculating the week wise total attendance by a measure 

 
Total Weekly Attendance =
AVERAGEX(
    KEEPFILTERS(VALUES('Date'[WeekNum])),
    CALCULATE(COUNTA('Attendance'[Emp Id]))
)
Now, how to divide this weekly number dynamically from working days from second table.

How to do this?