Skip to content
All projects

Project 04 // Analytics · 2026

Lancaster County Economic Indicators

Benchmarking a county economy against the state and the nation

Lancaster and Pennsylvania unemployment rates draw from 2015 to 2025; Lancaster stays below the state every year, 7.3% vs 8.9% in 2020.
Avg. private pay, 2014–2024 (not inflation-adjusted)
$41K → $59.5K
NAICS sectors
24+
Geographies
3

In short

The question

How is Lancaster County's economy performing relative to the state and the nation, and which industries drive its output and wages?

What I built

  1. 01
    BLS + BEA data
    • BLS and BEA public data
  2. 02
    Excel workbook
  3. 03
    Power Query
    • Power Query
  4. 04
    Multi-fact model
    • Multi-fact modeling
  5. 05
    Power BI reportUnemployment, pay and employers by sector
    • Power BI

What it found

Average private-sector pay (US$ thousands)
  • 2014~$41K
  • 2024~$59.5K
  • Lancaster's unemployment rate stayed below Pennsylvania's from 2015 to 2025, including during the 2020 spike.
  • Average private-sector pay grew from about $41K (2014) to about $59.5K (2024).
  • The largest employers (health care, manufacturing, retail) pay below the county average, while the highest-paying sectors employ fewer people.
  • Power BI
  • DAX
  • Power Query
  • BLS
  • BEA

Full case study // tap a section to open it

01Problem & context

How is Lancaster County's economy performing relative to the state and the nation, and which industries drive its output and wages?

Economic development staff brief investors, officials, and partners on how the county is doing. They need a trusted, side-by-side view of Lancaster against Pennsylvania and the U.S. on jobs, output, and pay, built from official public data.

Built as a technical interview case study for the Center for Regional Analysis at EDC Lancaster County, before I joined the team as a Data Analyst Fellow.

An interactive Power BI report that benchmarks Lancaster County, Pennsylvania against the state and the nation on the labor market, real GDP by industry, and wages, using public data from the Bureau of Labor Statistics and the Bureau of Economic Analysis.

I built it as a technical interview case study for the Center for Regional Analysis at EDC Lancaster County, before joining the team as a Data Analyst Fellow.

Side-by-side comparisons let economic development staff see quickly where Lancaster outperforms or lags, in a format ready for presentations to investors, officials, and partners.

02My role & approach

Solo project, built as a technical interview case study: data preparation, the multi-fact model, benchmarking measures, and a two-page report.

  1. 01

    Pulled BLS Local Area Unemployment Statistics (2015–2025, monthly), BEA real GDP by county and industry (chained 2017 dollars), and BLS QCEW wages.

  2. 02

    Modeled a star-schema variant with several fact tables sharing dimensions, so labor, GDP, and wage data filter together.

  3. 03

    Built a measures table for benchmarking: industry share of total, Lancaster ÷ PA, and Lancaster ÷ U.S.

  4. 04

    Designed a two-page report: a labor market page that toggles between Lancaster, Pennsylvania, and rate views, and an economic growth page for GDP, wages, and employment.

Problems I hit and how I handled them

  • Three sources at different grains (monthly labor data, annual GDP by industry, annual wages by NAICS sector) combined in one multi-fact model with shared geography, industry, and date dimensions.
  • A dedicated measures table for benchmarking: industry share of total, Lancaster ÷ PA, and Lancaster ÷ U.S.
  • Keeping comparisons honest: GDP is in chained 2017 dollars and the wage data covers private establishments only, and both are documented.
03Tech stack & data model
  • Power BI Desktop

    Two report pages (60 visuals) with toggles between Lancaster, Pennsylvania, and percentage-rate views, a custom Power KPI visual, and a navy theme.

  • Power Query

    Shaping seven Excel sheets: unpivoting, date parsing, type casting, and combining them into model tables.

  • DAX

    Benchmarking measures: industry share of total, average annual rate, Lancaster ÷ PA, and Lancaster ÷ U.S.

  • BLS LAUS and QCEW

    Monthly labor force, employment, and unemployment (2015–2025), plus private-sector wages by NAICS sector.

  • BEA CAGDP9

    Real GDP by county and industry in chained 2017 dollars, 2001–2024, for Lancaster, PA, and the U.S.

  • Excel

    Source workbook holding all seven datasets.

Data model

A star-schema variant with several fact tables sharing dimensions, so labor, GDP, and wage data can be filtered together by place, industry, and date.

  • Fact table

    fact_employment

    Monthly labor force, employment, unemployment, and unemployment rate by geography.

  • Fact table

    fact_gdp

    Real GDP by industry line code and geography, in thousands of chained 2017 dollars.

  • Fact table

    fact_lan_wages

    2024 Lancaster average employment and annual wages per employee by NAICS sector.

  • Fact table

    lan_avg_annual

    Lancaster average annual private-sector pay, 2014–2024.

  • Dimension

    dim_geo

    Lancaster, Pennsylvania, and United States, keyed by FIPS.

  • Dimension

    dim_industry

    NAICS codes mapped to sector names for GDP and wage analysis.

  • Dimension

    dim_date

    Date, month, and year for monthly and annual views.

  • Measures

    _Measures

    Share-of-total and Lancaster-versus-benchmark ratios.

Skills shown

  • Power BI
  • DAX
  • Power Query
  • Multi-fact modeling
  • BLS and BEA public data
  • Economic benchmarking
04What I learned & would improve

Lessons

  • Federal series arrive on different schedules and geographies; shared date and geography dimensions keep them comparable.
  • Toggles on one page (Lancaster, Pennsylvania, rates) are easier to read than several near-identical pages.

What I would do next

  • Refresh the data straight from the BLS and BEA APIs on a schedule, the approach I now use at EDC.
  • Add peer counties so Lancaster is compared with places like it, not only with the state.

Try it // runs in your browser

Write your own code

Annual unemployment rates (percent) for Lancaster County and Pennsylvania from the public series shown in the dashboard. Edit the code and run it.

Table unemployment: year, lancaster, pennsylvania

Ctrl + EnterLoading data...

Results appear here (first 10 rows). In Python, the last expression is shown, and print() works too.

Explore the report

Rebuilt from the project's data so it opens in any browser, no login. Use the filters and every visual recalculates.

Power BIThanh VuLancaster County Economic Indicators
Export .pbixReading view

Geography

Annual unemployment rate, 2015–2025

Lancaster average annual private-sector pay, 2014–2024

Microsoft Power BI1 of 4

See it in Power BI

The real report in Power BI Desktop. The subtitles explain each click.

Power BI Desktop // subtitles on // tap a step to jump

01 // Labor market

02 // Economic growth

Walkthrough

What each dashboard and diagram shows, and why it matters. Click an image to enlarge it.

  1. 1

    Labor market: Lancaster view

    The landing page: KPI cards, annual labor force and unemployment, and monthly trends, with date, year, and location controls.

    • Toggle buttons switch every chart between Lancaster, Pennsylvania, and percentage-rate views.
    • The 2020 unemployment spike and the recovery after it are clear in both the annual bars and the monthly lines.
  2. 2

    Labor market: Pennsylvania view

    The same page with the statewide data, for a direct comparison with Lancaster.

    • Switching views keeps the layout identical, so differences between the county and the state stand out immediately.
  3. 3

    Rate comparison

    Labor-force growth and unemployment rate, Lancaster against Pennsylvania.

    • Lancaster's unemployment rate ran below Pennsylvania's every year from 2015 to 2025, including during the 2020 spike.
    • This is the core message for EDC stakeholders: Lancaster’s unemployment rate stays below the state’s.
  4. 4

    Economic growth: GDP and wages

    Top industries by real GDP, the GDP trend for Lancaster, PA, and the U.S., and Lancaster's wage trend.

    • Average annual private-sector pay in Lancaster rose from about $41K in 2014 to about $59.5K in 2024.
    • A sector table ranks 2024 wages by NAICS sector, with management, utilities, and finance at the top.
  5. 5

    Bottom 5 industries and benchmarking

    The smallest GDP contributors, plus an industry table comparing Lancaster with Pennsylvania and the U.S.

    • Lancaster ÷ PA and Lancaster ÷ U.S. columns show each industry's weight locally against its benchmark.
  6. 6

    Employees by sector

    Which industries employ the most people in the county.

    • Health care, manufacturing, and retail are the largest employers but pay below the county average.
    • The highest-paying sectors (management, utilities, finance) employ far fewer people, a useful insight for workforce strategy.
Next projectRFM Customer Segmentation
Esc

↑ ↓ to moveEnter to open