Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 @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
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
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
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.
Use something along the lines of:
Measure = AVERAGEX(FILTER('Table',[Yellow]<>"NA"),[Yellow])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |