Calculate average in a matrix table

[Anonymous]

Hi all,

I am calculate a average value in a matrix table. Every worker has record, 1 or 0(Column 0 or 1). If there is any record not 0, it count 1(Column Max of 0 or 1). What I want is average as showing in the picture. I spent a lot of time to write a measure, but it is always wrong. Really need help!!

Thank you in advance!

Bran

Average =

VAR _count =

    ISINSCOPE ( 'Test'[Name] )

VAR _MAX = if(max('Test'[0 or 1])=1,1,0)

RETURN

    IF (

        _count,

        MAX ( 'Test'[0 or 1]),

            DIVIDE(calculate(COUNT('Test'[Name]),_MAX=1),distinctcount('Test'[Name]))

       )

Job Desc	Name	0 or 1
Foreman	Allan Doole	0
Foreman	Allan Doole	0
Foreman	Allan Doole	0
Foreman	Allan Doole	0
Foreman	Allan Doole	0
Foreman	Ben Ju	1
Foreman	Ben Ju	0
Foreman	Ben Ju	1
Foreman	Ben Ju	1
Project Manager	Andy Young	1
Project Manager	Andy Young	0
Project Manager	Andy Young	0
Project Manager	Andy Young	0
Project Manager	Craig Thorsen	0
Project Manager	Craig Thorsen	0
Project Manager	Craig Thorsen	0
Project Manager	Craig Thorsen	0
Project Manager	Craig Thorsen	0
Safety Coordinator	Abednego Shu	1
Safety Coordinator	Abednego Shu	0
Safety Coordinator	Abednego Shu	0
Safety Coordinator	Abednego Shu	0
Safety Coordinator	Abednego Shu	0
Safety Coordinator	Abednego Shu	0
Safety Coordinator	Andrew Hadley	0
Safety Coordinator	Andrew Hadley	1
Safety Coordinator	Andrew Hadley	1
Safety Coordinator	Andrew Hadley	0
Safety Coordinator	Andrew Hadley	0

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please try

Average =
AVERAGEX (
    VALUES ( 'Test'[Name] ),
    CALCULATE ( MAX ( 'Test'[0 or 1] ) )
)