Forum Discussion

skoalit's avatar
skoalit
Regular Visitor
9 years ago

How Average () is calculated in Power BI

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! 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      skoalit
      Regular Visitor
      9 years ago

      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's avatar
    BhaveshPatel
    Super User
    9 years ago

    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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi skoalit，

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

        Thanks,
        Lydia Zhang