Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate average in a matrix table

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 DescName 0 or 1
ForemanAllan Doole0
ForemanAllan Doole0
ForemanAllan Doole0
ForemanAllan Doole0
ForemanAllan Doole0
ForemanBen Ju1
ForemanBen Ju0
ForemanBen Ju1
ForemanBen Ju1
Project ManagerAndy Young1
Project ManagerAndy Young0
Project ManagerAndy Young0
Project ManagerAndy Young0
Project ManagerCraig Thorsen0
Project ManagerCraig Thorsen0
Project ManagerCraig Thorsen0
Project ManagerCraig Thorsen0
Project ManagerCraig Thorsen0
Safety CoordinatorAbednego Shu1
Safety CoordinatorAbednego Shu0
Safety CoordinatorAbednego Shu0
Safety CoordinatorAbednego Shu0
Safety CoordinatorAbednego Shu0
Safety CoordinatorAbednego Shu0
Safety CoordinatorAndrew Hadley0
Safety CoordinatorAndrew Hadley1
Safety CoordinatorAndrew Hadley1
Safety CoordinatorAndrew Hadley0
Safety CoordinatorAndrew Hadley0

 

 

 

 

Help me
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Please try

     

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

     

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 
    Please try

     

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