Hello
I'm fairly new to Tableau and have read everything I can find on this subject in the Community, but nothing has yet answered my specific question. I'm working my way through the video tutorials, but am achieving lots through trial and error!
Some background
I am currently creating a workbook that will help explore, analyse and present data from a survey done using Surveymonkey (about 40 questions, around 20,000 responses so far). The survey is still running, so I'm using the Web Data Connector to periodically import all the survey data and I'm trying to avoid manually exporting response data from Surveymonkey.
My question / plea for help
One of the key questions in the survey asks the respondent to score their satisfaction from 1 to 10 using a Matrix / Rating scale question:
Surveymonkey presents the data using a weighted average:
I'd like to replicate the above view as closely as possible in Tableau, with the ability to filter on answers to some other questions we've asked.
The response data is presented in a single column in the data source:
I'm managing to do lots of other interesting and useful visualisations with this data, but the weighted average has rather got the better of me. I understand the method of calculating the weighted average, and can do it in Excel, but I can't replicate it in Tableau.
I would be very grateful for any helpful suggestions.
Other info
I'm using Tableau Desktop 10.5.5 on a Mac, but can't upload a workbook for data protection / data security reasons.
Hi James,
I'm going to make two assumptions:
1) The data Tableau is computing over has one record per respondent for Q37. You can get this from the raw data downloaded from SurveyMonkey or pivoted data in Tableau that is then filtered for Q37.
2) You have a column that is either Q37 or an "answer value" (I'll call it answer value for now) that is numeric with the values from 1-10. If the survey data is using 99 or -99 or whatever for values for Skipped then you'll need to make sure those are filtered out as well. So the data that Tableau is computing over is really "one record per usable response".
In that case then AVG([answer value]) will get you the 8.39 weighted average. FYI Steve Wexler uses this exact contstruction in the section on Likert scales https://www.datarevelations.com/using-tableau-to-visualize-survey-data-part-2.html but doesn't explicitly say *why* that's used as the measure. The reason why is that Tableau isn't doing the average of numbers from 1-10, but instead the average across all of the records that have values from 1-10 so it's appropriately weighting for the 17K usable responses.
Jonathan
