Calculate Average if Denominator Meets Certain Critieria

[CityCat35]

Teachers at a special needs school are evaluated based on the performance of their students. Here's how it works:

• Each student has all of their test scores averaged, which computes to a letter score (i.e., think A, B, C, D, F)
• Student grades are averaged to give teachers a score between 1 and 5 (i.e., A=5, B=4, C=3, D=2, F=1)
• Kids are absent all the time, so if the children haven't completed enough work, they don't get a grade.
• For a teacher to get evaluated, at least half of their students have to have a letter grade

Problem: I need a formula that will only calculate a teacher's evaluation score IF at least half of her students have a letter grade. Otherwise, she would be evaluated as "Not Enough Information." Is there a DAX formula for this scenario. Sample data is below

Average Grade	Evaluation Score
F	1
D	2
C	3
B	4
A	5

Teacher	Student	Average Grade
Ms. Johnson	James	A
Ms. Johnson	Molly	C
Ms. Johnson	Avery	A
Ms. Johnson	Jack	B
Ms. Johnson	Billy	No Grade
Ms. Johnson	Penny	A
Ms. Johnson	Scott	C
Ms. Johnson	Willow	No Grade
Ms. Johnson	Jennifer	A
Ms. Johnson	Thomas	No Grade
Mr. Allen	Grace	B
Mr. Allen	Aiden	No Grade
Mr. Allen	Tyler	A
Mr. Allen	Dallas	No Grade
Mr. Allen	Oliver	No Grade
Mr. Allen	Bailey	No Grade
Mr. Allen	Olivia	No Grade
Mr. Allen	Chris	A
Mr. Allen	Quinn	C
Mr. Allen	Alex	No Grade

[RossEdwards · Accepted solution]

For my solution i would suggest merging the 2 tables into one.  You can do this with the "Merge Queries" in Power Query.  This would mean you should have two columns now, an Average Grade and Evaluation Score in your main table.

First we need a measure to figure out if a teacher should be considered:

Valid Data = VAR noGradeRows = CALCULATE(
    COUNTROWS('YourTable'),
    'YourTable'[Average Grade] = "No Grade"
)
VAR allRows = COUNTROWS('YourTable')
RETURN
DIVIDE(noGradeRows, allRows) <= 0.5  // This measure will return either TRUE or FALSE

Now we need a measure that calculates the average grade

Average Grade = CALCULATE(
    AVERAGE('YourTable'[Evaluation Score]),
    NOT 'YourTable'[Average Grade] = "No Grade"
)

From here, if you create a table visual, with the first column being the teacher and the last column being the following measure:

Teacher Grade = VAR selectedTeacher = SELECTEDVALUE('YourTable'[Teacher])
VAR validData = IF(   // Does a check that only a single teacher is selected and that teacher has valid data
    ISBLANK(selectedTeacher),
    FALSE(),
    [Valid Data]
)
VAR result = IF(
    validData,
    [Average Grade],
    0
)
VAR output = SWITCH(
    INT(result),  // Round down the average grade
    0, "Not Enough Information.",
    1, "F",
    2, "D",
    3, "C",
    4, "B",
    5, "A"
)
RETURN
output