How Average () is calculated in Power BI

[skoalit]

Hi, 

Does anyone know if 'Null' values are included in the calculation of Average? And the best way to treat 'Null' and 'Blank' values, that when I do division or average, it will not show 'Infinity'? 

Thank you! 

[Anonymous]

Hi @skoalit,

I make a test using the following sample data.


When I average the values of column in a table visual, the blank values are not included in the calculation of Average, you can check the following screenshot to get details.



Moreover, when you do division, you can override 'Infinity’ by specifying the third parameter in Divide function so to return a fixed value such as 0. There is an example for your reference.

Column3 = DIVIDE(10,Table4[Column2],0)



Thanks,
Lydia Zhang

[skoalit]

@Anonymous Thank you so much once again! 

However I prefer not to hard code to find the average as I would like it to be more interactive. I hope to know more about how Power BI handle blank or null values, whether it is considered in the calculation? 

Thank you! 

[BhaveshPatel]

See this BLOG on how to handle blank values. You can create a measure for counting and summing rows that do not include blank using ISBLANK to except those rows that are blank.

[skoalit]

Thanks! this is helpful! 

[Anonymous]

Hi @skoalit，

You should get detailed information about how Power BI handle blank values from the blog that BhaveshPatel provides.

Thanks,
Lydia Zhang

[Anonymous]

Can someone help me with the average of person wise for four weeks data as mentioned below in the source file.

If you see below screenshot i want average of four weeks with respect to employee name ... remember it has null values. Thanks