Forum Discussion
True/False indicator based on a measure?
Hi everyone,
I have tried searching and I don't think I have the right vocabulary to find just what I need, so I hope you can help me!
I have data like the attachment (well, I don't see anywhere to add an attachment, so I'll come right back after I post this). I have the following measure:
AbsRate = calculate(COUNT(table[DATE]), filter(table, table[Attendance]="Absent"))/calculate(COUNT(table[DATE]))
So, depending on the context, AbsRate would show the percentage of absences in an entire school, grade level, or individual student.
I need to figure out how many students (unique StudentID) have an AbsRate of 10% or higher. I might end up with 3 measures:
- Total number of students = count unique studentID
- Total number of students chronically absent = count unique studentID if absrate >=.10
- Percentage chronically absent = total number of students chronically absent / total number of students.
Can anyone help me create these measures based on this data format?
5 Replies
StudentID Date Attendance 11111 1/1/2023 Present 11111 1/2/2023 Present 11111 1/3/2023 Present 11111 1/4/2023 Present 11111 1/5/2023 Present 22222 1/6/2023 Present 22222 1/7/2023 Present 22222 1/8/2023 Present 22222 1/9/2023 Present 22222 1/10/2023 Absent 33333 1/11/2023 Present 33333 1/12/2023 Present 33333 1/13/2023 Absent 33333 1/14/2023 Absent 33333 1/15/2023 Absent I don't think I can attach a file, so here's how the data look. Thanks!
Hello amberebee,
1. Total number of students (count unique StudentID):
Total number of students = DISTINCTCOUNT(table[StudentID])2. Total number of students chronically absent (count unique StudentID if AbsRate >= 0.10):
Total number of students chronically absent = CALCULATE( DISTINCTCOUNT(table[StudentID]), FILTER(ALL(table), [AbsRate] >= 0.10) )3. Percentage chronically absent (Total number of students chronically absent / Total number of students):
Percentage chronically absent = DIVIDE([Total number of students chronically absent], [Total number of students], 0)Hi Sahir,
Thanks so much for engaging with my question! Here is the outcome when I create the three measures you provided. Each row represents a different school. The number of students column is correct. I'm still trying to figure out what's going on with the other 2 columns, but roughly speaking, the number of students chronically absent in a school should range from about 8-30% of the total number of students. There's something off about the context of the measures that I haven't figured out yet.
https://drive.google.com/file/d/1iK84pkUeAh7jBwEw7xWRiosgPoPnVdgx/view?usp=sharing
OK, let's see if this works.
I apologize for not including the school variable in my initial question. I didn't realize it mattered.
Here's what you will see in the linked report. AbsRate is calculating properly in the top table, but the # and % of students chronically absent does not vary by school the way I want. School A has 2 students, 67% chronically absent. School B has 3 students, 100% chronically absent.
It does seem like % chronically absent is fine, it's just the count of students chronically absent that is messing up the %. It's dividing 5 (number of chronically absent students in the whole division) by 3 (number of students in a particular school)
