cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
IdRatherBHiking Frequent Visitor
Frequent Visitor

Calculate Percentage of Meets / Exceeds Surveys per month

I have a measure that calculates the percentage of Met expectations + Exceeded expectations / total number of surveys submitted.

I've been asked to show this total month over month graphically.  

The measure is as follows:

Exceeds / Met Surveys = CALCULATE(DIVIDE(
COUNTROWS(
                                     FILTER(
                                         ALLSELECTED('CorrectiveTrends (3)'),
                                        SEARCH("Exceeded expectations",[Rate your overall satisfaction with the service provided:],1,-1) > 0
                                     )
                                ) + COUNTROWS(
                                     FILTER(
                                         ALLSELECTED('CorrectiveTrends (3)'),
                                        SEARCH("Met expectations",[Rate your overall satisfaction with the service provided:],1,-1) > 0
                                     )
                                ) ,COUNTROWS(FILTER(ALLSELECTED('CorrectiveTrends (3)'),[Rate your overall satisfaction with the service provided:] <> ""))))
When I add the measure to a graph and apply the month as an Axis, only the overall score is shown each month. Can someone please tell me how I would go about showing the percentage of
positive responses for each month?
1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Calculate Percentage of Meets / Exceeds Surveys per month

Hi @IdRatherBHiking ,

 

 

the problem is ALLSELECTED. This formula removes the month filter and only keep the filters selected as slicer. As the month filter is removed, each row will have the same (total) number.

 

Was there a reason to add ALLSELECTED?

 

If not, can you try removing it and letting us know if that fixes it?

 

Regards,

 

LC

Interested in Power BI templates? Check out my blog at www.finance-bi.com

View solution in original post

4 REPLIES 4
Super User II
Super User II

Re: Calculate Percentage of Meets / Exceeds Surveys per month

Hi @IdRatherBHiking ,

 

 

the problem is ALLSELECTED. This formula removes the month filter and only keep the filters selected as slicer. As the month filter is removed, each row will have the same (total) number.

 

Was there a reason to add ALLSELECTED?

 

If not, can you try removing it and letting us know if that fixes it?

 

Regards,

 

LC

Interested in Power BI templates? Check out my blog at www.finance-bi.com

View solution in original post

IdRatherBHiking Frequent Visitor
Frequent Visitor

Re: Calculate Percentage of Meets / Exceeds Surveys per month

Hi @ lc_finance, Thanks for your help with the ALLSELECTED, I’m new to DAX and thought that this was needed to select all records. Removing this from the measure has helped a bit.  I’m now getting a total per month, but its diving the total score across the 12 months now. Jan 8.45; Feb 9.05; Mar 6.79; Apr 6.97; May 10.3; Jun 8.49; July 9.18; Aug 9.07; Sep 6.33; Oct 8.36; Nov 7.6; Dec 4.64; total 95.23. I’m hoping to show a total per month, such as 95% compliance for January, etc.

IdRatherBHiking Frequent Visitor
Frequent Visitor

Re: Calculate Percentage of Meets / Exceeds Surveys per month

Hi @ lc_finance, I was able to figure it out. Using your revised calc above, I created a new measure Compliance per month and used an AVERAGEX on the new measure. This seems to be doing exactly what I wanted. I'm going to mark your first answer as the solution. Thanks for all your help!

Super User II
Super User II

Re: Calculate Percentage of Meets / Exceeds Surveys per month

Wonderful! I am glad this worked out for you.

 

Do not hesitate if you have additional questions.

 

Regards,

 

LC

Helpful resources

Announcements
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.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors