Forum Discussion
averageif
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
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] ) ) )
6 Replies
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] ) ) )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 !
ClementOk, 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.
Hi,
Drag Column1 to the Table visual and write this measure
=AVERAGE('table'[column2])
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
