Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Can anyone help me create these measures based on this data format?
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)
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.
I replicated your measures in Power BI using the simpler example dataset, and they worked. I'm going to expand the simple dataset to also include different schools to see if I can better replicate the issue in my larger dataset. Stay tuned.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |