Looker studio calculated field formula (extremely diff)

Question

I have a test that I administer and the results are represented in 3 columns: colA contains names of students and colB contains decision string which is either correct or incorrect, and colC contains class teacher name.

We want to assess the class teachers performance for the group of students which fall under them.

I want to write a formula for calculated field where I want to see how many passed and how many failed under the class teacher. For example Teacher A has 5 students under her, Teacher B has 10 students under him etc. I want a field which shows me how many out students under teacher A passed and how many failed.

I tried:

IF( ROUND ( SUM( CASE WHEN CORRECT/INCORRECT = 'Correct' THEN 1 ELSE 0 END ) / COUNT ( CORRECT/INCORRECT ) * 100, 1 ) >= 80, 'Passed', IF ( ROUND SUM( CASE WHEN CORRECT/INCORRECT 'Correct' THEN 1 ELSE 0 END ) / COUNT ( CORRECT/INCORRECT

• 100, 1 ) = 0, ' Incomplete', 'Eligible for retest'))

I utilized this formula to calculate the scores (above 80 one passes) and give the students the tag of passed or not passed.

But I am unable to calculate how many students under a teacher passed. Like teacher A has 5 students and say 3 passed then I want to show in a column 60% of them passed (3/5).

Answer 1

There is probably a smarter way to do this, I have a work around,

1. Create a tables A with colC class teacher name

2. Create a measure called "Students" using count_distinct on colA add this to your table. This should show a count the number of students each teacher has

3. Copy and past the table add a filter for "passed" based on your formula above, Rename the "Students" as "passed". This table should only include a count of students who passed the test.

4. Select both tables, right click and select blended data. This should create a blended data table with Teacher name, Students and passed

5. Add a table from that blend with the Teacher name and then click on add metric and add a field called "pass rate" where you divide passed by Students. This table should now show Teacher name and Pass rate

Let me know if this worked for you