averageif

[ClementSendra]

Hello,

I try to integrate a column in a table which would calculate an average depending on the value in a column.

I know that it is possible to use a formula like : 

CALCULATE ( AVERAGE ( 'table', 'table[column2] ), FILTER ( 'table', 'table'[column1]="X" )

That's great but i don't want to precise a specific value like ( "X"), but compare the value of each row.

In an Excel table i would do : =AVERAGEIF([column 1],"="&[@[column 1]],[column 2])

Thank you inadvance.

Clement

[Ashish_Mathur]

Hi,

Drag Column1 to the Table visual and write this measure

=AVERAGE('table'[column2])

[j-mag]

Hello,

How do you get this to work in a table with 10+ columns of data? Given all the columns it doesn't average, just shows the result matching the column I need the average for.

Regards,

James

[AlB · Accepted solution]

Hi @ClementSendra

Can you show with an example what you need?  A small table with sample data and the required result.

I don't understand what you mean by

... i don't want to precise a specific value like ( "X"), but compare the value of each row.

Maybe this:

NewColumn =
CALCULATE (
    AVERAGE ( 'table'[column2] ),
    FILTER ( 'table', 'table'[column1] = EARLIER ( 'table'[column1] ) )
)

[ClementSendra]

Thank you for your message,

For each row, i want to compare the value in [column1] and calculate an average of the [Column2] of all the rows having the same value in [column1]

In excel :

Column 1         Column 2         Average

      c                      6                      6                             Average = AVERAGEIF([Column 1],"="&[@[column 1]),[column 2])    

      a                      1                      1

      c                      2                      6

      b                      2                      3

      b                      4                      3

      c                     10                     6

Thank you for your help !
Clement

[AlB]

@ClementSendra

Ok, so both answers above should work. Mine if you want it in a calculated column and @Ashish_Mathur's if you want it with a measure.

[ClementSendra]

Thank you very much it is exactly what i was looking for !

Clement