calculate average by omitting empty cells and the word "does not apply"

[Syndicate_Admin]

Hello Dear community, hoping you are well, I have the following question:

I need to calculate the average of a column, but this one has 3 options: empty cells, word "does not apply" and percentages.

The question is this: how can I calculate the average by omitting the empty cells and the word "does not apply"?

in front of you, thank you very much

[Portrek]

Hi syndicate_admin.

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.

In this Community has a post the Lydia Zhang talk about the average with values null and blancks. You can see below..

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)

I wiat can help you.

Best Regards.

[Syndicate_Admin]

thanks for the answer, but it has not been very clear to me, it is possible that I can generate a formula, taking into consideration the aforementioned?

[v-xiaotang · Accepted solution]

Hi @Syndicate_Admin 

you can create a column, then use the dax expression mentioned by Portrek.

FYI:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns

if problem still persists or you need more tutorials, please let me know. 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.