Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

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

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"?

Jos_Valds_0-1625680954240.png

in front of you, thank you very much

1 ACCEPTED SOLUTION

Hi @Syndicate_Admin 

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

vxiaotang_0-1626081174185.png

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.

View solution in original post

3 REPLIES 3
Portrek
Resolver III
Resolver III

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.

Portrek_0-1625681773622.png

 



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.

Portrek_1-1625681774063.png

 




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)

Portrek_2-1625681774118.png

 

 

I wiat can help you.

Best Regards.

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?

Hi @Syndicate_Admin 

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

vxiaotang_0-1626081174185.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.