Introduction
It’s week 17 and we’re back to create what demographers refer to as an Age Pyramid, in which each bar represents the makeup of the US population as a whole in 5 year cohorts. The inspiration for this visual came from the Pew Research Center’s 2014 publication in which they visualized “America’s Morphing Age Pyramid.”
According to the US Census Bureau’s 2017 population projections, in 2030, 1 in every 5 residents will be retirement age*. Older people are projected to outnumber children for the first time in US history. Looking forward, the United States’ age pyramid is looking more like a rectangle as a result of longer life spans and lower birth rates.
Today we’re going to use two custom visuals in Power BI to create an animated Age Pyramid using data from the US Census Bureau.
Thanks to everyone for your continued participation in #WorkoutWednesday – Power BI edition! Please be sure to fill out the submission tracker at this link.
*source: US Census Bureau
Requirements
- Transform the data so that you have four columns in your data table
- Year
- Population
- Sex
- Age
- If you would rather start with a Power BI desktop that includes the modeled data, click here
- Create a measure to calculate the percent of population
- Add two custom visuals to the report:
- Tornado Chart
- Play Axis (dynamic slicer)
- Add a tornado chart including the fields Age, Sex, and % of Population
- Adjust the size of the canvas to fit your age pyramid
- Add the Year to the Play Axis visual and set the animation settings to auto-start and loop
- Add a card visual to display the year
- Ensure that your data labels are clear and remove extraneous text
- Get creative with your design!
Dataset
Today’s challenge uses data from the US Census Bureau’s 2017 projections representing the years 2015 – 2060. You will either connect to this excel file or use this starter Power BI Desktop (.pbix) file. Note that those starting with the Excel file will need to do a bit of data modeling and writing DAX measures. The .pbix file includes the usable data model.
Share
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.
Solution
– for assistance with Power BI measures please download the .pbix file
The solution You Tube solution release seems delayed?
NO You Tube Solution?
There is no solution You Tube for several weeks?
Hi SATYAVRAT – apologies for the missing video. I will post it this upcoming weekend (by July 12). Thanks for asking! I am sure others are looking for it too! We appreciate your patience – we are volunteers and sometimes life gets busy!
Still awaiting…..
Hi SATYAVRAT – please start by taking a look at the .pbix file. In between a full time job, community contributions, and surviving a global pandemic, I am really trying my best! I appreciate your follow-up and hope that you and others are able to use the .pbix as a guide.
Hi Shannon,
I was to create a Tornado chart but I was unable to sort it via Age.
Hi! Looks very good! Just one question: how do you add the age index to sort the graph?
Never mind! I was looking within the graph instead of using the ‘sort by column’ feature.
Was the Youtube video for this ever added?
Hi Jo! Unfortunately I wasn’t able to create the video for this week. Please take a look at the solution file!