Hello all,
I am trying to generate a report which determines chronic absence rates by school. I have a table called StudentAttendance and in this table I have StudentID, CalendarDate, AttendCode, SchoolID, and a column called isAbsence which is 1 if the AttendCode is for an absence and is 0 for all other AttendCodes.
I managed to come up with a table visualization to display a student's number of absences, number of days enrolled, their absence rate and a column called isChronic which is 1 if the absence rate is greater than or equal to 10% and 0 otherwise. All of this is dependant upon a date slicer because depending on the range provided in the slicer a student may or may not be chronically absent. The definition of chronically absent is a student who is absent 10% or more of their enrolled attendance days.
I'm trying to take the above data and aggrigate it at the school level to determine the chronic absentee rate based on the school's population. So for example, if for a particular slicer date range a school has a population of 100 students and during that range 4 students were defined as being chronically absent, then that school has a chronic absentee rate of 4%.
I'm having trouble trying to get the data to aggrigate properly up at the school level. I was able to do it at the student level and I was hoping to take that student level aggrigate to calculate the building level, but it's not working.
Any ideas?
