Forum Discussion
Custom column - Average excluding "NA" values
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
Use something along the lines of:
Measure = AVERAGEX(FILTER('Table',[Yellow]<>"NA"),[Yellow])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.
Hi Anonymous,
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
4 Replies
Use something along the lines of:
Measure = AVERAGEX(FILTER('Table',[Yellow]<>"NA"),[Yellow])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.
Hi Anonymous,
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
Hi Anonymous,
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