cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mstuve Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User III
Super User III

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

Hi @mstuve

 

 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
Super User III
Super User III

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

Hi @mstuve

 

 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

mstuve Regular Visitor
Regular Visitor

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

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! 
Super User III
Super User III

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

@mstuve

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?

 

mstuve Regular Visitor
Regular Visitor

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

AllExcept is to filter by year. 

 

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

Super User III
Super User III

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

@mstuve

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors