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 Arka! You need to create a column in your data that contains a sort order. For example, age <5 is 1, 5 to 9 is 2, 10-14 is 3, and so on. You'll then use the Sort by Column option to get your age pyramid sorted properly! https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-desktop
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!
Please explain what is the logic behind this and what we want to achieve with this.
Display year =
IF (
HASONEVALUE ( dimCensusYear[Year] ),
FORMAT ( MIN ( dimCensusYear[Year] ), “0000” ),
FORMAT ( MIN ( dimCensusYear[Year] ), “0000” ) & ” – ”
& FORMAT ( MAX ( dimCensusYear[Year] ), “0000” )
)
Also, please explain is it necessary to create separate tables for Sex, Age, Year.
Hi Bharat, thanks for your question.
It is not necessary to create separate tables for Sex, Age, and Year. You could do this with one flat table, however, building out a dimensional model is a good practice to get into in Power BI, because this is the optimal way that the engine (under the surface) functions. Here’s a great article to start your learning: https://learn.microsoft.com/power-bi/guidance/star-schema
The measure you reference is what I used in the display in the top right corner, so that the census year displays in the card as the image is played.
Here’s a breakdown:
– It checks if there is only one value selected in the “dimCensusYear[Year]” column using the HASONEVALUE function.
– If only one year is selected, it formats that single year as “0000” (e.g., 2024 would be displayed as “2024”).
– If multiple years are selected, it formats the minimum and maximum years as a range separated by “–” (en dash), both formatted as “0000” (e.g., if the range is from 2020 to 2024, it would be displayed as “2020 – 2024”).
So, the purpose of this measure is to dynamically display either a single year or a range of years based on the user’s selection from the “dimCensusYear” dimension.
I hope this helps!