Understanding Demographic Distribution using PowerBI

Public and private institutions have long relied on data about people to have a better understanding of the population. Whether it is the National Census Bureau or a startup company's user database, institutions have been relying on their database to gather and infer insights about the population. This exercise uses descriptive and diagnostic analytics in PowerBI to better understand the population distribution of an organization's demographic, and what information can be taken away from the analysis.

Executive Summary

The sample population contains data of more than 32k people, 2/3 of which are men and the remaining third are women. 85% identify as White, 9.59% are Black, and the remaining are a combination of Native Americans, Asians, and other ethnicities. The dataset has more young people in it, with the 21-30 year olds being 28.65% and 31-40 year olds being 23.82% of the sample for women, and 23.30% of the 21-30 year old 27.44% the 31-40 year olds for men. 69.70% of the population work in the private sector. 76% of the population are earning under $50k annually, and 61.58% are working more than 40 hours a week. More than half of the sample are at least a high school graduate, with the average individual spending 10 years of studies. Among all ethnicities, Asians on average spent more years in higher education and worked more hours per week for the 50 years old and below cohorts. While married individuals are the largest cohort in our sample, a large portion of the population under 31 years old are single and are never married. This rings true for both genders. Meanwhile, a large number of divorces occur from ages 40-45 years of age.

Young people 25 and below typically work less hours per week versus their older cohort. People in executive positions have the highest number of divorced and never married individuals across all industries. Similarly, divorced individuals work the most hours per week across all occupations and industries.

Background of Study

Understanding demographic data can provide socioeconomic information important for research in understanding communities at the present, and where these communities are headed. These insights can serve as a guide for policy-making, planning, and decision-making (Veroff, n.d.).

Problem Statement

The file contains data of a sample population. We have no previous information of the population demographics and aim to understand more about the people in our database in order to inform future policies and decision-making.

Analysis Objective

This analysis aims to understand its demographic composition. The analysis will cover age, ethnicity, education, marital status, gender, hours of work, employment status, occupation, capital gains and losses, and annual earnings. The analysis aims to discover insights and correlations of earnings to these demographic factors.

Key Metrics

This research gives emphasis on earnings; segmenting the group into two based on incomes. The first group earns under $50k annually, and the second group earning more than $50k annually.

Methodology

Data Sources

The data is from a .CSV file locally stored and not connected to a cloud or database network. It is a single data file that does not require a schema to relate to other databases.

Data Preparation and Cleaning

The data was cleaned and checked for inconsistencies including capitalization, spacing, spelling, and data types. Inconsistent data were cleaned and incomplete data removed. The data was cleaned and transformed using Power BI's Power Query to check for data type, column quality, column distribution, and column profile for a broad check up of the data quality. SQL was not necessary in this case as the .csv file is in a local storage and not stored in a cloud or networked server.

A column was deleted as the data is irrelevant to the study. This is done to optimize the data load times. In order to group the data according to age, Age bins with 10-year widths were created.

Analytical Approach

Using Power BI's DAX language, additional columns were created: Age Group = SWITCH(

    TRUE(),

    adult[Age]<=20, "under 20",

    adult[Age]>20 && adult[Age]<=40, "21-40",

    adult[Age]>40 && adult[Age]<=60, "41-60",

    adult[Age]>60 && adult[Age]<=75, "60-75",

    adult[Age]>75, "75+"

)

Translating the raw data into text for Annual Earnings column: Annual Earnings = SWITCH(

    TRUE(),

    adult[income]= "<=50k", "Less than $50k",

    adult[income]= ">50K", "More than $50k"

)

Interpreting education level based on the number of years of study: Education Level = SWITCH(

    TRUE(),

    adult[Education Years]<9, "High School Undergraduate",

    adult[Education Years]=9, "High School Graduate",

    adult[Education Years]>9 && adult[Education Years]<13, "Some College",

    adult[Education Years]=13, "Bachelors",

    adult[Education Years]>13, "Masters/PhD"

)

Interpreting employment status based on the number of hours worked per week: Employment = SWITCH(

    TRUE(),

    adult[Hours per Week]<=20, "Part-time",

    adult[Hours per Week]>20 && adult[Hours per Week]<=40, "Full-time",

    adult[Hours per Week]>40, "Full-time 40+"

)

Slicers for gender, earnings and age groups are deployed throughout the report to allow the user to breakdown or drill into specifics of these categories. Various visualizations are utilized to present data findings. These include cards, bar charts, donut charts, tables, stacked bar chart, bar and line graphs, tree maps, geographic maps, and scatter charts.

Key Findings & Insights

Overview

Analysis by Age

Analysis by Ethnicity

Analysis by Education

Analysis by Marital Status

Analysis by Hours Worked per Week

Key Influencers between Less Than $50k earners and different factors

Key Influencers between More Than $50k earners and different factors

Segments of Population with Less Than $50k Earnings

Segments of Population with More than $50k Earnings

Download the exported PDF version of the PowerBI visualizations here.

Analysis and Interpretation

The study has 32.56k subjects. Within that population, 85.43% are ethnically White, 9.59% are Black, 3.19% are Asian/Pacific Islander, 0.96% are Native American and the remaining are other ethnicities. 66.92% of the subjects are male, and 33.07% are female.

The age distribution of the subjects are right-skewed, with the median female age at 35, and the median male age at 38. 23.30% of men and 28.65% of women are aged 21-30; 27.44% of men and 23.82% of women are in the 31-40 age range; 22.62% of men and 19.07% of women are aged 41-50. These three age groups are the largest age group cohorts. Asian/Pacific Islanders, on average, receive more education than any other ethnicity. The average years of education in the entire population is 10 years, only Asian/Pacific Islanders and White ethnic groups have cohorts that have above average years of education. More than half of the population are high school graduates and/or received higher education at 54.69%.

69.70% of the population work in the private sector. 61.58% of the population are full-time workers, clocking in 40 hours or more weekly. Despite this, 75.92% of the population are earning less than $50k annually, with most of these subjects being young and unmarried/single individuals. Individuals with a bachelors degree or higher typically work more than 40 hours. People who earn more than $50k annually on average work more hours regardless of their educational background. The average weekly hours dedicated to working for more than $50k earners is 45.47 hours. More than 15,000 subjects are married, the biggest group in the population, followed by single or never married subjects. This tracks with the age distribution skewed towards the younger population, as 70.15% of men and 69.98% of women under 30 years old have never been married. The number of divorced individuals spike in the 31-40 and 41-50 year old cohorts for both males and females. Divorced individuals, both male and female, tend to work more than 40 hours a week, with female executives dedicating an average of 43 hours at work and male executives working on average 48.2 hours weekly.

Executive jobs have the second highest number of hours dedicated to working every week, behind agricultural work by only a margin. Trade and administrative jobs are industries where subjects consistently work full-time 40 hours. Women and men tend to work less as they get to middle age, regardless of their earnings. The decline in work hours get steeper for men, with their work hours halving from 75 hours a week at age 30 to 33 hours a week as they reach 60 years of age.

Using Power BI's Key Influencers, factors were identified what influences earnings to be less than $50k annually and more than $50k annually. individuals who have received 2.58 years less of the average education are 2.54 times more likely to earn less than $50k. Single/never married individuals are 1.44 times more likely to earn less than $50k as well. Black, Native American, and other ethnicities have 1.18, 1.16 and 1.20 times likelihood to earn $50k or less. Divorced, widowed, or separated individuals all have a higher likelihood of earning less.

On the other hand, married individuals are 6.84x more likely to earn more than $50k annually. Subjects with more than 12 years of education (or an associate's degree) are 2.53 times more likely to be earning more. In the same vein, subjects who work more than 49 hours a week are 2.19 times more likely to be earning more than $50k, and being of White ethnicity has a 1.65 times likelihood of earning more than other ethnicities. Asians and Pacific Islanders are 1.23 times more likely to earn more than $50k compared to other ethnicities.

Conclusion & Recommendations

Conclusion Summary

We have 32. The population is right-skewed, with more younger people below 50. The median age for males is 38 years old, while the median age for females is 35 years old. Males outnumber females, with males comprising 2/3 of the population. 89.58% stated their origin country as the United States. Only 15% of the population is non-White, with Black people the biggest minority group at 9.59% followed by Asian/ Pacific Islanders at 3.19%. The average individual has 10 years of education. Asians, on average, have received higher education more than other ethnicities. Only Asians and White ethnic groups have subjects with above average years of education. Married individuals are the largest subgroup, with 15.4k individuals. This is followed by single or never married subjects with 10.6k individuals, and 4.4k divorced. Majority of the single or never married subjects are under 40 years of age, while the number of divorced individuals rise at age 41 onwards.

75.92% of the population earn less than $50k annually. Of all working individuals, 69.70% of the subjects work in the private sector. 61.58% of the population work full-time 40 hours or more weekly. Occupations in agriculture, transportation and executive positions are more likely to work more than full-time hours. On the opposite end, trade and administrative/clerical jobs have consistent full-time hours and rarely do over-time work. Subjects who earn more than $50k annually spend more hours working than that of their counterparts. This rings true regardless of their education background. Men are more likely to work more hours weekly than women, with the younger cohort (21-30 years) trending towards working more than 60 hours a week. The number of hours sharply trends down to working 30 hours for subjects 51-60 years old. The number of hours worked are more consistent for women, with younger people 21-30 years old working more than 40 hours, and trends down to 37 hours a week for 51-60 years olds. Interestingly, only 8.33% of the entire population have liquid financial investments.

Regression analysis were done to explain factors for the likelihood of people earning less than $50k and more than $50k annually respectively. Education has a strong influence on one's earnings. An individual with 2.58 years less than the average 10 year education has 2.54x likelihood of earning less than $50k. Similarly, people working less than 34 hours a week, or are not married, have higher chances of earning less. Minorities also more likely to earn less, with non-Black people more prone to be earning less. In contrast to this, married individuals are more likely to be earning more than $50k annually, with a 6.84x likelihood to be earning above $50k. Subjects with 2.58 more years above the average years of education are 2.54x more likely to be earning more. People who work more than full-time hours, particularly working 49 hours a week are 2.19x likely to earn above $50k. White people are likely 1.65x higher earners, while Asians and Pacific Islanders have 1.23x likelihood to be the same.

Prioritized Recommendations

Educational Reform

Education is one of the biggest factors affecting salaries. The correlation is strong with the years of education to pay. We also see correlations in ethnicity and lesser pay. Black, Native Americans and other minorities are likely to earn less as they have received lesser years of education than the population average.

Addressing Disparity in Pay Based on Ethnicity

White people, regardless of educational background, are more likely to be earning more than $50k despite the facts that Asians tend to have spent more years in education and Asians on average working more hours. Further study is recommended to study other factors that affect differences in wages.

Addressing the Salary Gap

It is worth noting that 3/4 of the population are earning less than $50k, and the remaining earn more and also work more. Younger people tend to work more hours than older cohorts yet 93.55% of people 30 years old and below are earning less than $50k. Salary gaps between genders are also noticeable, with 91.69% of women under 40 years old are earning below $50k, compared to 79.49% of men under 40.

Improving Financial Literacy

With only 8.33% of the population having financial investments, it is recommended that we look into giving financial literacy across all demographics. This will improve the likelihood of individuals having more financial control over their spending, investments, assets, insurances, and retirement and further improve their quality of life. This is a worryingly low rate overall that could be corrected over the long-term. We can review and implement policies regarding financial literacy and financial management. We hope that as the demographic ages and earn more, we hope to see this number exponentially increase.

Appendix

Veroff, D. (n.d.). What you can learn about your community from demographics. Organizational & Leadership Development. https://leadershipdevelopment.extension.wisc.edu/articles/what-you-can-learn-about-your-community-from-demographics/