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
Anonymous
Not applicable

Don't Include Two Response Options in Measure Calculating Percentage Across Year

I am trying to calculate the percentage of each reponse for Q1 by year. For example, the number of people who responded 5-Strongly Agree in 2015, 2016, etc. 

 

I am using a clustered column chart as my visual with Survey Year as the legend, Q1 as my axis, and I created a new measure for the value:

 

#ofRespondentsT = DIVIDE(COUNT(Satisfaction_Survey[Q1]), CALCULATE(COUNT(Satisfaction_Survey[Q1]), ALLEXCEPT(Satisfaction_Survey,Satisfaction_Survey[SurveyYear])))
 
When I format my measure to % and compare my chart to a matrix, the numbers in the chart are lower because in the matrix, I filter out BLANK and NA - Not Applicable responses. for the Percent of Column Total. 
 
How do I filter out BLANK and NA - Not Applicable responses in my measure so anyone who responsed with these are not calculated in the percentage/total? 
 
Below is a sample of my data. Thank you! 

 

example data.jpg

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @Anonymous

 

 COUNT( ) already ignores blanks and as for "NA - Not Applicable", you can add a filter condition to your CALCULATE:

      Satisfaction_Survey[Q1] <> "NA - Not Applicable"

or else you could use a slicer with [Q1] to deselect "NA - Not Applicable"  

 

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

Hi @Anonymous

 

 COUNT( ) already ignores blanks and as for "NA - Not Applicable", you can add a filter condition to your CALCULATE:

      Satisfaction_Survey[Q1] <> "NA - Not Applicable"

or else you could use a slicer with [Q1] to deselect "NA - Not Applicable"  

 

Anonymous
Not applicable

Thank you. When I try to add the slicer for Q1, it just adds/subtracts from the x-axis since I have Q1 response options for this axis. I cannot use my measure as a slicer even though this is what I want to filter. 

 

When I add a filter to CALCULATE, I am getting an error because I don't know how to do this properly. My error is that <> is incorrect.  Also, BLANK is listed in the data as an actual response and not blank/null. It is treated the same as NA - Not Applicable. 

 

#ofRespondentsT = DIVIDE(COUNTA(Satisfaction_Survey[Time]), CALCULATE(COUNTA(Satisfaction_Survey[Time]), ALLEXCEPT(Satisfaction_Survey,Satisfaction_Survey[SurveyYear]), <> "NA-Not Applicable"))
 
Thank you! 

@Anonymous

You need the full version of what I wrote, not just half of it:

   

Satisfaction_Survey[Q1] <> "NA - Not Applicable"

 

Also, what is the ALLEXCEPT( ) for?

 

Anonymous
Not applicable

AllExcept is to filter by year. 

 

Sorry. I am very new to this, and I don't usually write code. 

@Anonymous

Hmmm... I'm not sure you understand how to use ALLEXCEPT correctly. Can you paste the data in your image above in text format here? So that it can be readily copied to run a couple of tests. It's always better to do include data that can be copied or the pbix file itself. It makes things much easier for people trying to help. You can copy a part of your table and paste it in your post.     

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.