2021 Week 17 | Power BI: Age Pyramid

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

11 thoughts on “2021 Week 17 | Power BI: Age Pyramid”

  1. Shannon Lindsay

    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!

  2. 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.

    1. Shannon Lindsay

      Hi Jo! Unfortunately I wasn’t able to create the video for this week. Please take a look at the solution file!

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top