Forum Discussion
AVERAGE
Hi All, I get two different outputs while using AVERAGE. In excel, i get a different value and in PowerBI a different value. Can I know why that is happening? I have attached the images belo...
If you are creating a
ABC = DIVIDE(AVG2[col1]+AVG2[col2]+AVG2[col3]+AVG2[col4]+AVG2[col5], IF(ISBLANK(AVG2[col1]),0,1)+ IF(ISBLANK(AVG2[col2]),0,1)+ IF(ISBLANK(AVG2[col3]),0,1)+ IF(ISBLANK(AVG2[col4]),0,1)+ IF(ISBLANK(AVG2[col5]),0,1) ,BLANK())calculated column then you may need to use this.
FarhanAhmed Thank you!
Can I also know how to correct the formula so as to get the same result as excel?
In Divisor you need to put something like
= VALUES / ( COUNT(column1) + COUNT(Column2) + COUNT(column3) .... COUNT(Column9))
this count will give nothing when it finds null values. so it will divide your data by 5 instead of 9. if all columns have some value then it will divide by 9
If you are creating a
ABC = DIVIDE(AVG2[col1]+AVG2[col2]+AVG2[col3]+AVG2[col4]+AVG2[col5], IF(ISBLANK(AVG2[col1]),0,1)+ IF(ISBLANK(AVG2[col2]),0,1)+ IF(ISBLANK(AVG2[col3]),0,1)+ IF(ISBLANK(AVG2[col4]),0,1)+ IF(ISBLANK(AVG2[col5]),0,1) ,BLANK())calculated column then you may need to use this.
Thanks a lot! FarhanAhmed