Color has meaning in data visualization. If you’ve ever viewed or created a choropleth map, you’re familiar with encoding data so that different colors in geographic areas represent different values. While a map is not an appropriate visual for all geographic data–and there are certainly problems with area-based representations–Power BI has numerous ways to try to visually map out your data. Let’s consider one today, but with a twist!
Typical choropleth maps represent a single measurement and have a color scale that manages a single spectrum of values. With this challenge, you need to use your Power BI skills to bin data into low/medium/high buckets, but you need to do so with two separate variables. You will then create a measure that combines your bins for both variables so there are now nine (3×3) possible options instead of only three.
Note that there are some limitations around using ArcGIS Maps for Power BI with the “Publish to Web” feature (Publish to web—ArcGIS for Power BI | Documentation), so your final map should look similar to the static image below.
I’ve also embedded a Publish to Web version with the native Filled Map, which doesn’t appear as stunning compared to ArcGIS. For interactivity and a tooltip demo for you, it gets the job done. It’s not as crisp or elegant though. Have you caught on that I prefer the ArcGIS map over the Filled Map yet? If the situation is right, you can do this challenge and so much more with ArcGIS Maps for Power BI.
- Read about key concepts behind Bivariate Choropleth maps first: Bivariate Choropleth Maps: A How-to Guide (joshuastevens.net)
- This challenge’s data is already based on percent of population, so don’t worry about normalizing on top of what is provided
- Select a color scale from among the article’s sample options
- In Power Query, merge the two datasets together on FIPS
- In Power Query, cleanup, transform, and add columns as needed to get data into shape
- Hint: You’ll need at least a County column as well as two that I called In Poverty and Completed College that contain numeric data
- After loading the data into your data model, add separate Bins for In Poverty and Completed College
- Note that this could be manually done in Power Query as well, but this demonstrates Power BI’s Groups/Bins feature as well as is easier to work with visually in the UI
- Create a measure that combines the 3×3 combination of values (9 total options)
- Hint: Use the DAX SWITCH function and combine with SELECTEDVALUE. Look at past WOW challenges for help with SWITCH as well. You should end up with nine conditions to match the nine bivariate options.
- Use ArcGIS Maps for Power BI
- Note that some organizations may disable this particular map, in which case you could use the Filled Map as an alternative
- Create a choropleth map using Location (County) and Color (your measure)
- Find and set the map colors to your chosen color scale in the Symbology section
- Mind the map’s tooltip
- Mind the data formatting for percentages
This week uses two separate tables available in the same dataset from data.world. Originally sourced from the US Department of Agriculture website, it contains data for Michigan’s 83 counties for % of population in poverty and % of population who have completed a college education.
Use Power BI’s native data.world connector to import both the poverty_2019 table and the percent_college_completion table, which you then must join together in Power BI’s Power Query Editor.
WOW2021week15 – dataset by dataveld | data.world
After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. 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.