Custom column - Average excluding "NA" values

[ugurgulluev]

Dear all,

I kindly need your expertise for creating a custom column that calcuates the average of several columns. The problem is I have a dataset that includes some "NA" values:

White	Black	Yellow	Red	Custom column (average of all colors)
3	6	6	8	Error
1	9	NA	7	Error

If I included the "Yellow" column when calculating the average, the custom column gives an error because of the "NA" value.

What I am trying is to exclude the cells with "NA" values.

Excel is automatically excluding the "NA" values while calculating the average, but it seems that PowerBI does not.

Do you guys think of a workaround for this?

Thanks for your support in advance.

Ugur

[Greg_Deckler · Accepted solution]

Use something along the lines of:

Measure = AVERAGEX(FILTER('Table',[Yellow]<>"NA"),[Yellow])

[sgsukumaran · Accepted solution]

Option 1: As pointed out use filter in your query

Option 2: Default value to 0 when blank or NA occurs that way your regular calculation would work.

[v-danhe-msft]

Hi @ugurgulluev,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @ugurgulluev,

Based on my test, you could refer to Greg_Deckler's solution, also, you could refer to replace the 'NA' value in query editor:

 

Hope it could help you.

Regards,

Daniel He

[sgsukumaran · Accepted solution]

Option 1: As pointed out use filter in your query

Option 2: Default value to 0 when blank or NA occurs that way your regular calculation would work.

[Greg_Deckler · Accepted solution]

Use something along the lines of:

Measure = AVERAGEX(FILTER('Table',[Yellow]<>"NA"),[Yellow])