This month we are getting the #WorkoutWednesday community involved in the challenges with Community Month. Each week we’re going to have an outstanding member of our community present a challenge. This week we have Gerard Duggan from sunny down under (Australia). Gerard rocks the Power BI community showing us how to present outstanding analysis and insights in Power BI through his participation in many community data visualisation challenges as seen on LinkedIn and Twitter. Gerard is a mining resources project manager with KBR has a background in data analysis and e-discovery in construction arbitration.
This week we are looking are visualising survey data, in particular data collected in the form of a Likert scale.
You may or may not be familiar with this term, but you will likely have answered a survey or questionnaire where you are requested to answer using a score out of 5 or 10, or whether you agree, disagree or are neutral on something. This kind of closed question with a sliding scale can be referred to as a Likert scale, which are named after their creator, Rensis Likert.
Below are two links on Likert scales and Net Promoter Scores (NPS) for those that want to know a little more.
In this exercise, we will transform the survey data into a diverging bar chart to visualise the distribution of answers received in the survey.
- Download dataset or link to it – https://github.com/MartinaGiron/TDS-code/raw/main/working-with-surveys/Random%20Survey%20Data.xlsx
- Transform data in Power Query with following steps:
- Promote headers.
- Unpivot queries.
- Create duplicate table and assign sentiment to each query (hint at steps below)
- Right click and remove duplicates
- Assign Index
- Create list of negative sentiment items
- Create custom column
- Merge tables as new master table, and expand to show Sentiment.
- Assign number value 1-5 to the query answers using conditional column – remembering sentiment.
- Close and Apply.
- Create a Table in Desktop for the required measures:
- Positive, Neutral and Negative Counts. [Note Neutral values to be split in two to diverge across y-axis, and Negative Count to have a negative value.]
- Create a Total value that can be applied to each query using ALLEXCEPT filter to allow % values to be calculated for each query group
- Create the % Scores for Positive, Neutral and Negative Values using DIVIDE.
- Create an NPS Score, which is (100*[Positive Score] + [Negative Score])
- Option to create a Rank Calculation to order the bar chart and NPS by the NPS score.
- Create Visuals with divergent stacked bar chart and an NPS visual (matrix or bar chart). Be wary of the order of adding measures to the x-axis [hint to start with negative values first, and move away from zero, then add the positive values].
- Optional to create different methods or combinations of visuals and add context or narrative.
The challenge uses a generic sample survey dataset available on github . You can either download the data as an excel file, or link directly within Power BI using the web connector
After you finish your workout, share on Twitter using the hashtags #WOW2022 and #PowerBI, and tag @DGAnalysis @JSBaucke, @MMarie, @shan_gsd, @KerryKolosko, and @NerdyWithData. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
Please be sure to cite source before sharing.