Distinct count a field based on a criteria on another columns' aggregation

[Anonymous]

Hi PowerBI experts! How do you distinct count a field based on a criteria on another columns' aggregation?

example:

StaffID	Score
101	56
101	43
102	12
101	15
103	43
104	31
104	25
102	19
101	23
101	16

Desired output:
Distinct Count of StaffID where Average of Score is greater than or equal to 20.

Thanks in advance

[FreemanZ]

hi @Anonymous 

you can also create a measure with this:

Avg20PlusCount = 
VAR _table =
ADDCOLUMNS(
    VALUES(TableName[StaffID]),
    "ScoreAvg",
    CALCULATE(AVERAGE(TableName[Score]))
)
RETURN
COUNTROWS(
    FILTER(
        _table,
        [ScoreAvg]>=20
    )
)

it worked like this:

[DataInsights · Accepted solution]

@Anonymous,

Try this measure. The VALUES function returns distinct StaffID, enabling COUNTROWS to return distinct count.

Distinct Count StaffID = 
VAR vTable =
    ADDCOLUMNS (
        VALUES ( Table1[StaffID] ),
        "@Average", CALCULATE ( AVERAGE ( Table1[Score] ) )
    )
VAR vResult =
    COUNTROWS ( FILTER ( vTable, [@Average] >= 20 ) )
RETURN
    vResult