cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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:

 

WhiteBlackYellowRedCustom column (average of all colors)
3668Error
19NA7Error

 

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

3 ACCEPTED SOLUTIONS
Super User IV
Super User IV

Use something along the lines of:

 

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Resolver II
Resolver II

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.

View solution in original post

Microsoft
Microsoft

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:

 1.PNG

 

Hope it could help you.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft
Microsoft

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:

 1.PNG

 

Hope it could help you.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Resolver II
Resolver II

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.

View solution in original post

Super User IV
Super User IV

Use something along the lines of:

 

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors