Having Trouble Calculating Survey ResultResponse Rate
Rachel Sarabia Ortiz Mar 12, 2020 10:57 AMI am trying to create a dashboard that displays count of respondents, count of people surveyed and a response rate. Currently, I am using a filter and a parameter to make this work. I cannot figure out the calculation I need to make the response rate work correctly using just my filter on college.
Can someone walk me through what I might be missing, what I can do to make this calculation work? Thank you in advance for your time and your help.
Total Number of Respondents Calculated using the following LOD:
{ FIXED [College Location Primarily Work],[ResponseId]: [Total Numerator (copy)]}
Total Numerator Calculation:
SUM({ FIXED [ResponseId]: MIN([Weight])})
Weight Calculation:
case [College Location Primarily Work]
when " College 1" then 1.170816
when "College 2" then 0.790835
when "College 3" then 0.901423
when "District Office" then 0.648912
else 1
END
Total Number of Employees Surveyed Calculated using the following LOD:
{ FIXED [College Location Primarily Work]: MIN([Total Denominator])}
Total Denominator Calculation:
IF [College Location Primarily Work]= "College 1" then 1379
ELSEIF [College Location Primarily Work]="College 2" then 323
ELSEIF [College Location Primarily Work]= "College 3" then 337
ELSEIF [College Location Primarily Work]= "District Office" then 122
ELSE 4 (these are the respondents who did not identify which college they were from)
END
Response Rate Calculated using the following LOD:
SUM([Numerator LOD])/SUM([Denominator LOD])
Response Rates were not calculating correctly. I wanted Response Rates to be the following:
College 1 29.9%
College 2 29.7%
College 3 30.0%
District Office 29.5%
Unknown 100.0%
Grant Total 30.0%
So, I created the following calculation:
Response Rate with College Filter
CASE [College Location Primarily Work]
WHEN "College1" then [C1 Number of Respondents Weighted]/[C1 Number of Employees Surveyed]
WHEN "College2" then [C2 Number of Respondents Weighted]/[C2 Number of Employees Surveyed]
WHEN "College3" then [C3 Number of RespondentsWeighted]/[C3 Number of Employees Surveyed]
WHEN "District Office" then [DO Number of Respondents Weighted]/[DO Number of Employees Surveyed]
ELSE [Unknown Number of Employee Respondents]/[Unknown Number of Employees Surveyed]
END
The Response Rate was still not calculating correctly for the Grand Total Which Represents All or DistrictWide Results.
So, I created the following parameter:
And used the following calculation to get the response rate above:
Response Rate with Parameter
CASE [Parameters].[College Location Primarily Work]
WHEN "All" then [Weighted Total Number of Employee Respondents (copy)]/[Weighted Total Number of Employees Surveyed]
WHEN "College1" then [C1 Number of Respondents Weighted]/[C1 Number of Employees Surveyed]
WHEN "College2" then [C2 Number of Respondents Weighted]/[C2 Number of Employees Surveyed]
WHEN "College3" then [C3 Number of RespondentsWeighted]/[C3 Number of Employees Surveyed]
WHEN "District Office" then [DO Number of Respondents Weighted]/[DO Number of Employees Surveyed]
ELSE [Unknown Number of Employee Respondents]/[Unknown Number of Employees Surveyed]
END

Survey Response Rate Issue.docx 272.1 KB