Calculate average number of attempts to get sufficient per course

[Anonymous]

I have the following table:

studnr/course/result

102	A	4,6
103	A	6,6
101	A	3,7
105	A	9,1
101	A	7
102	B	4,4
102	B	5,4
104	B	4,6
105	B	3,9
105	B	6,1
104	B	7,8
101	B	3,1
101	B	3,5
101	B	7,6
102	B	3,1
102	C	3,3
102	C	4,5
102	C	6,3
104	C	5,6
105	C	5,6
101	D	5,3
101	D	4,6
104	D	4,9
105	D	3,1
105	D	4,5

I need to calculate the average number of attempts needed to get a sufficient per course.

This is, quite simply, the following formula:

No. of Attempts / No. of Sufficients

I've done this by using a measure but I did not get the right answer since the No. of Attempts should only be counted if the student also has got a sufficient.

Hopefully this is enough information to go on.

[jdbuchanan71 · Accepted solution]

@Anonymous 

Is this the same question as this post?

https://community.powerbi.com/t5/Desktop/Apply-filter-on-measure/m-p/847800#M406924

If so, the solution should be the same.

Avg to sufficient = 
VAR _sufficient = 5.5
VAR _Students = 
    FILTER (
        SUMMARIZE(Results,Results[Student],Results[Course]), 
        CALCULATE(
            MAX ( Results[Result] )
        ) > _sufficient 
    )
VAR _Classes = CALCULATE ( COUNTROWS ( Results ), _Students )
RETURN DIVIDE ( _Classes, COUNTROWS( _Students ) )

I have attached my working file for you to take a look at.

[Anonymous]

Thank you for your answer, it is indeed correct.

Sorry for posting a duplicate question, I searched for it but could not find it.