Measure to calculate average

Question

Okay, so I have this dataset.

What I want to do is checking the average number of attempts a student has to take to get to a 6 or higher. So far I came up with: Students = CALCULATE(DISTINCTCOUNT(StudentResults[studnr]);StudentResults[result]>=6) Which is how many students passed each class. In SQL, I'd use a Group By and a Having Max. How can I implement this in a measure in Power BI?

Answer 1

To calculate the average number of attempts a student needs to achieve a successful result (>=6) for a course, we need to divide the total number of attempts by the number of succesful attempts.

When counting the total number of attempts, we need to discard the attempts that have not yet led to a successful result, because you cannot count the number of attempts it took to achieve a successfull result if this succesfull result has not been achieved yet.

So the pseudo formula is AVG #Attempts = (all attempts - #No Success Yet) / #Successful Attempts

These are the three base measures:

All Attempts = COUNTROWS ( 'StudenResults' )

#Succesfull Attempts = COUNTROWS ( FILTER ('StudenResults', 'StudenResults'[result] >= 6))

#no succes yet =
SUMX (
    'StudenResults',
    CALCULATE (
        IF ( MAX ( 'StudenResults'[result] ) < 6, 1, 0 ),
        ALLEXCEPT ( 'StudenResults', StudenResults[studnr], StudenResults[course] )
    )
)

This measure calculates the requested average:

AVG #Attempts = DIVIDE([All Attempts] - [#no succes yet], [#Succesfull Attempts], BLANK ())

A matrix visual, with [course] on Rows and the measures on Values, would look like this:

If you want the average for each student, just put [studnr] on Rows in stead of [course]