Forum Discussion
Calculate average and include blanks
Hi all, I can't figure out the correct DAX function to use for this calculation so hopefully someone can point me the right way. Below is a screenshot of what I'm trying to achieve. It's actual...
I know the post is very old (2017), but I stumbled upon it when looking into the same issue.
The difference between Excel and Power BI is that Power BI ignores blanks and zeros when calculating the average while Excel does not. There might be a better way, but I was able to solve it by manually calculating the average as the sum of values divided by the count of elements.
Note: The column you use for the count should not include blanks, otherwise you need to ensure Power BI also counts the blanks (e.g. COUNT + COUNTBLANK). Then in Power BI it would become: SUM([values]) / ( COUNT([values]) + COUNTBLANK([values]) ).