Calculate average in a matrix table

Anonymous — Thu, 28 Jul 2022 17:26:57 GMT

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

Re: Calculate average in a matrix table

tamerj1 — Thu, 28 Jul 2022 18:23:49 GMT

Hi Anonymous
Please try

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

topic Calculate average in a matrix table in DAX Commands and Tips

Calculate average in a matrix table

Re: Calculate average in a matrix table