Satisfaction Rate Calculation In Power BI (Likert Scale)

[fereshtehaghaei]

I am fairly new BI users. I am working on students evaluation survay.

I have all this built in Tableau and now need to re-built my dashboards in Power BI. Is this even doable in power BI or are there different or less steps to take to get this result:

My Tableau Calcualted Field Steps in order to get me the Satisfaction Rate fields, in another word I had to take these steps to get the right count for my numerator and right count for denominator; therefore, as a result I could divide them and get the Satisfaction Rating %:

1. Combined ResponseID Question ID = [Response Unique ID]+"-"+str([Question ID])
2. Promoter = IF [Response] = "Strongly Agree" OR [Response] = "Somewhat Agree"
                       THEN [Combined Response Question ID]

                       ELSEIF [Response] ='Not Applicable' THEN [Combined Response Question ID]

                       END

3. Numerator= COUNTD ( IF [Category]="CA" THEN [Promoter ] END)

4. Denominator= COUNTD([Combined ResponseID Question ID])

5. Satisfaction Rate % = Numerator / Denominator

How Would I acheive this in Power BI, I have followed and tried multiple ways and calculations but nothing gives me the desired results. I have a sample of my Power BI file attached so if anyone want to look into this, you can see all my Power BI (DAX) calculations. I even re-did my table connections but nothings seems to give me the result I am looking for.

I really would appreciate it if anyone want to tackle this and help me out please.

https://drive.google.com/file/d/18udf1Ygl-PDC9Jba5-f8ymvzFcdW-w5S/view?usp=drive_link

 

[_AAndrade]

I don't have data with [Category]="CA". Let's concentrate in one Agency firt.
For Mercy Hospital we have this responses:

First measure is distinct count of all responses and "New Promoter" it's my new measure than only counts if the responses is "Strongly Agree", "Somewhat Agree", or "Not Applicable".

How I should do to calculate the Satisfaction Rate % ?

[fereshtehaghaei]

To calculate the Satisfaction Rate % we need to treat the new promoter as the numerator and divide it by SUM # of RESP (since # of RESP is the count of the respondents) Does that make sense? I do have # of RESP as a measurment and as a column. I believe my issue is in the numerator (Promoter) formula.

I changed my model so I will update the file on google doc as well this is the new model:

 

[_AAndrade]

Hi,

Please check if any satisfaction rate is what you're looking for:

[fereshtehaghaei]

Based on what I have in Tableau to compare these are the correct results:

The highlighted blue is Agency and the table on the right of it are the Units for that particular Agency's Satisfaciton results. Does this help at all. I truly appreciate you helping me. 

@_AAndrade  I have also updated my table connections and I uploaded the new file, incase you need to take a look at the new file, I was adviced to update my connections and was told that what I had wasn't looping correctly.

 

 

[_AAndrade]

Please show me the right numbers do you have (numerator and denominator) to get this percentages

[fereshtehaghaei]

Hi @_AAndrade 

Sure, I be more than happy to share that data, here is the screenshot from my Tableau's numerator and denominator for both Agency and Unit. I included behind the data for Arkansas Children's Northwest (denominator) and (numerator) so you can see what results I got in an excel file based on thos Tableau's calculations, maybe that would be hepful to write it differently in Power BI so we can get the same way of counting respondents based on their Agency selection, Unit Selection and the questions their asnwered and their respond was Strongly Agree and somewhat agree. I uploaded the result excel file in the Google drive and here is the link:

https://drive.google.com/file/d/1Kcgjn326KSc_VgI1zt0YHuJ9Ob1jsJUv/view?usp=drive_link

 

[_AAndrade · Accepted solution]

Hi,

Finally, I'm getting the same values as you.
The final result is this:

Measures:

As you can see in the matrix for Arkansas you have 34 rows but you only consider 24. With your example I found that you only count if the column "Satisfied Socores" is different to "" (empty), therefore I used this criteria to do my denominator. In your first mensage you have a different criteria using "Category"="CA" but I didn't find any row with that information.
In my solution I'm using your first pbix and only makes some changes on the relations. The final model is this:

I hope this could solve your problem.

[fereshtehaghaei]

@_AAndrade  I changed the following and then applied your solution and worked except a tweak in the table model in order for the Filters to filter the # of Enrollment this is the Model built that worked for my filters:

And then For denominator, I had a field called Satisfied Score that was conditional column that I built in tranform data section and I accidanlty chose Filtered Questions instead of Question Column, when I was editing that's why when I swited my partners, the data was coming back as Null or blank. THANK YOU SO MUCH for your continous help, solutions and not giving up and not ignoring it!! I will accept your solution so hopefully you Rank will go higher and higher you certainly deserve it!!

[_AAndrade]

Thank you for your kind words.  It's my pleasure to help.

[fereshtehaghaei]

Thank you so much @_AAndrade  this gave me the exact results for satisfaction rates, numerator numbers and denominator numbers are all showing up correctly. I feel that you know this dataset so well and would it be possible to tell me why after a few changes I did in the data model changes when now that I go to my Response Rate tab, my Enrollment number doesn't change and now that the Satisfaction Rate number are correct the # of Enrollment and REsponse Rate is not calculating right. Any solution for that? I change everything and I saved over it so I can't go back but here are the screen shots with the right number of response and Enrollment. You mentioned in your last response you couldn't find the Category called 'CA', the category for this survey would be called 'CLAG' not sure if that would help with anything that is happenning here. Also, how can I have Satisfaction Result for a different Parnter, if I change the Partner from HUY to MDUY all the denominator values and Satisfaction Rate % changes to zero.

This shows Mercy's Hospital # of respondent and # of enrolled students and the Response Rate%

This shows the # of respondent and # of enrollment for Med/Surg unit in Mercy hospital

I had these numbers calculations right and it's under Response Rate page tab and this is what's happenning right now: Since the number of Enrollment is not being counted correctly it's messing up the Response Rate Calculation. Currenlty this is what's showing up in Power BI 

 

 

I would appreciate it if you can tell me what happend and why did this happend .

 

[fereshtehaghaei]

@_AAndrade  Thank you so much! I will follow these steps right away! Is there a way you can send this edited/updated Power BI file back for my reference please?

[fereshtehaghaei]

@_AAndrade As I mentioned I have built these in Tableau so I have those screenshots and correct results. As it shows in the image. Mercy Hospital has 77% Satisfaction Rate and then when I click on Mercy Hosptial I will see the satisfaction rate for it's units as shown in the image students rated 5 different unit. So I need Satisfaction Rate as an overall for Hosptial(Agency) then by selecting I want to see it breaking down so I can see satisfaction rate for it's units. thank you for taking the time and helping, I have included my Power BI project link in Google drive and you can also see all my table connections as well as all the DAX formula's I have tried and still no luck to acheive this result.

1st Image shows Satisfaction Rate for Agency:

2nd Image shows once I click on Mercy Hospital is showin it's units satisfaction rate:

What I have in Power BI as a result which is not correct outcome

 

 

 

[_AAndrade]

Hi, I'm trying to help you but I can't understand what you really want to do in your calculations.
Please give me same pictures with the correct results and I will try to do it in power bi.