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

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.

Reply
afaherty
Helper IV
Helper IV

Ignore blanks when calculating %

Hello All,

I have researched this for hours but have been unable to resolve it.  Any help would be appreciated.

I have survey data.  Of course, sometimes respondents do not answer every question. 

Here is an example of my data:

Respondent IDThe class was very helpful
1Strongly Agree
2Agree
3 
4Agree
5Strongly Disagree
6Agree
7Strongly Agree
8 

 

I have a bar graph displaying the % of respondents who answered with each response.  It is sorted in order (Strongly Agree, Agree, Disagree, Strongly Disagree)

 

Here is my dax:

% = DIVIDE([RespondentIDUniqueCount],CALCULATE([RespondentIDUniqueCount],ALL('Survey'[The class was very helpful],'Survey'[The class was very helpful Order Sort])))
 
The problem is that I don't want those who didn't answer the question to be included in the calculation or the bar graph, for example, like this where it shows (Blank):
 
afaherty_0-1703086925534.png

 

I do not want to simply filter out the blanks in the visual because then the total percentages do not equal 100%, of course. 

I should also add that I do not want to remove the entire row, as the person may have answered other questions on the survey but left that particular question blank.

 

Thank you for any help you can offer!

2 REPLIES 2
Dangar332
Super User
Super User

HI, @afaherty 

 

use Visual level filter like below image made from your data

in advance filter option choose isnotempty for The class was very helpful column

Dangar332_0-1703091257630.png

 

Thank you, but as I mentioned, that means then that the bars in the graph do not add up to 100%.  I would prefer to handle this via DAX.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors