Calculate percentage summary field - using only a specific value
I have a database looking at student test scores.
There is a record for each student test.
I have a layout that is using three sub-summary layout parts:
a) teacher name
Pass can either be "pass" or fail"
I have a summary field called "count" that sums the student ID, and have it on each sub-summary layout, so it reports each sub-category of performance.
I want to calculate the PERCENTAGE of students who PASSED for each teacher.
I created a calculation field "performance_value" that looks like this:
Round ( GetSummary ( count ; Pass )/GetSummary ( count ; Teacher ) ; 3 )*100
This works for most cases, because I sort the pass field by a value list definition, placing "pass" at the top.
However, if a teacher doesn't have ANY students that pass, it incorrectly states that the teacher had 100% pass, because it is grabbing the first summary value that it finds, and in this case it represents the number of students who failed.
So how can I more accurately/specificaly calculate the value showing percent of students that passed?