Average a column based on max value of second column grouped by unique values of third column

[Anonymous]

Hello,

I'm currently trying to find a way to calculate the average "static" pass rate of my dataset overall. The pass rate column is currently set up as a cumulative pass rate (see attached below) so basically I need to be able to look for the max value of "cumulative attempts" column for each individual in the "ID" column and then retrieve the overall test pass rate for that individual (1 line) so that I can run an average on each of those final pass rates. If I were to average a cumulative pass rate (as-is currently) the pass rate would be skewed lower than it should be overall. I unfortunately cannot link my dataset yet for privacy concerns but hope the image below should be enough, to understand the logic of what I'm trying to do.

Any help is much appreciated!

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

We can create a measure to get that by only one step:

Measure =
VAR P =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Result] = "P" )
    )
VAR al =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
    DIVIDE ( P, al ) + 0

[Anonymous]

Edit: I think I've figured it out actually...by breaking it up into two steps. 1) the following formula seemed to work to get static pass rates that would repeat for each person down the rows:

CALCULATE ( MAX ([Cumulative Pass Rate Column]), ALL EXCEPT ([ID column])

and from there I could run the average of that column (7 percentage points higher than my cumulative pass rate would've been).