Introduction
Welcome back to Power BI #WorkoutWednesday – we’re now in week 6! If you’re joining us for the first time, welcome! There are no dependencies on previous weeks – you’re welcome to jump right in with this week’s challenge, where we’ll use AI in Power BI to get an understanding of key influencers of a particular variable (pay)!
For the coming weeks’ challenges, we will be using Data Visualization Society‘s annual State of the Industry survey results. This week we’ll begin to explore the data, attempting to understand what influences annual compensation of respondents who work full-time in the industry.
The goal of this week’s challenge is to begin to explore this year’s survey results using AI in Power BI – with the key influencers visual.
Requirements
- This week’s challenge requires a Google account to use the Google sheets custom connector. If you do not have a Google account, download the Power BI Desktop (.pbix) file containing the data model.
- If you are pulling the data yourself, load data for the year 2021
- In Power Query, keep columns of interest that you suspect may influence annual compensation. I used:
- Role (column: Role (as employee)
- Education Level (column: EducLevel)
- Years of Data Viz Experience (column: YearsDVExperience)
- Gender (column: Gender_summarized)
- Member of historically disadvantaged or underrepresented racial or ethnic group (column: RacEthHistUnderrep_)
- Filter out respondents where Role (as employee) is blank. This will help us look only at individuals working in full-time roles. This step is optional – if you’re more interested in freelance workers or if you’d like to analyze both, go for it!
- The PayAnnual column contains 15 distinct pay groups. Narrow the number of groups down using either a conditional or custom column.
- I used groupings of $40k and stopped at $160k (grouped anything $160k or greater)
- Add a sort order column so your pay groupings appear in the correct order.
- Add the Key influencers visual. Use Annual Pay in the Analyze field and add other columns of interest in the Explain by field.
- Customize the formatting – I used colors from the Data Visualization Society logo.
- I use Colour Contrast Analyzer to ensure my colors are accessible. This also gives you hex values!
- Explore the results of the Key influencers visual. Look at the Top segments tab to see more detail.
- Get creative with your design and share!
Dataset
This week’s data comes from the Data Visualization Society’s annual State of the Industry survey. Data can be found in full (in Google sheets) here. Note, to pull data directly from Google sheets you will need a Google account.
If you do not have an account, you can download the .pbix containing the data. Note that data transformations have already been completed and you will not be able to access Power Query.
Share
After you finish your workout, share on Twitter using the hashtags #WOW2022 and #PowerBI, and tag @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.
Solution
Power BI Desktop Solution File
Solution video coming the week of Feb 14 💘
Another great one – never used this one before! Learned a few new things! Thanks!
did you bring in Member of historically disadvantaged or underrepresented racial or ethnic group (column: RacEthHistUnderrep_); i think you bring in OrgSize column as one of explain by.
Hi Solar! I used both columns in the explain by field well. I used 6 columns total: Years Data Viz Experience, Education Level, Role, Member of underrepresented racial or ethnic group, Gender, and Org size.