Power BI | Source and method
Five states hold 43% of US mineral production value.
A single-page Power BI report on the value of nonfuel mineral production by US state, built as a deliberately textbook star schema - and authored entirely in code rather than dragged together in Power BI Desktop.
The question
The US Geological Survey publishes the value of nonfuel mineral production for every state once a year. The raw table answers nothing on its own: it is 50 rows of dollars with a grand total, a pre-computed rank and a percent column already baked in.
This report asks one question of it - where is US mineral value actually concentrated, and how far does that concentration go? Every visual on the page is derived from a single fact table, so there is no second source to reconcile and no number on the page that cannot be traced back to one column of one file.
How it is structured
One page, four zones, each with a job. The layout is generated from a single geometry file, so the panels behind the visuals and the visuals themselves cannot drift out of alignment.
| KPI row and slicer | Total production value, how many states report, and which state leads - plus a census-region slicer that filters the whole page |
|---|---|
| Map, left column | Where the value is, by state. Bubbles sized by value with a value-driven colour gradient, pinned to a US view on a grayscale basemap |
| Ranked bar, top right | The top ten states in order, coloured by production tier, so the ranking and the tier read at once |
| Treemap, lower right | The same value grouped by census region, so regional weight is visible without a second slicer trip |
The model behind it
- One fact table.
fact_state_production, grain of one US state per production year. It holds keys plus the single base measure. - Dimensions describe the fact's own columns.
dim_geography(state, census division, census region, principal commodities) anddim_production_tier(Major at $5B and above, Mid at $1-5B, Minor below $1B). Nothing else is admitted. - Relationships are single-direction, one-to-many. Dimensions filter the fact; the fact never filters back.
- Five measures, no stored aggregates. Production value, share of total, state rank, states reporting, and the top producing state.
What it shows
- The 50 states produced about $105 billion of nonfuel minerals in 2025. The roughly $7B that USGS reports as "undistributed" sits outside state scope and is excluded.
- Nevada leads at $12.6 billion, about 12% of the 50-state total, on copper, gold and silver.
- Value is concentrated: Nevada, Arizona, Texas, Alaska and California together account for roughly 43%.
- By tier, only five states clear $5 billion. Twenty-two are mid-tier and twenty-three are minor - so the distribution has a long, thin tail rather than a broad middle.
Technical note
Why the ranks on this page disagree with the ranks USGS publishes
The source file ships its own rank column. This report ignores it and computes
rank with RANKX instead, along with the total and each state's
share. That is the star-schema rule applied strictly: totals, ranks and
shares are measures, not stored data, because a stored rank is only
correct for the filter state it was computed under, and this page has a slicer.
Following that rule surfaced something in the data. The source's own rank column disagrees with its own value column - roughly 11 to 13 states per release. Wyoming, in one release, ranks eighth nationally on a published $622 million, a figure that could not place it eighth.
The explanation is disclosure, not error. USGS withholds individual commodity values to avoid revealing company proprietary data, and pools the withheld amount into a national "undistributed" line - but it still ranks each state on its full value while publishing only the disclosable part. So for about 20 states the two ranks differ, and both are correct. They answer different questions: theirs ranks production, this one ranks disclosed value.
The method generalises further than the finding does. A single-column sanity check would never have caught this. Cross-checking two columns that should agree with each other is what exposed it, and it is now the first thing done to any new source.
Built as code
This report was not assembled by dragging fields in Power BI Desktop. Microsoft's PBIP format stores a project as plain text, so a program can write it. Python scripts generate the semantic model in TMDL (tables, relationships, measures) and the report in PBIR (visuals, theme, layout, cross-visual interactions). Desktop is opened only to refresh and publish.
- The ETL fails the build, not the report.
build_model.pyruns integrity checks and exits non-zero without writing anything if any of them fail. - Cross-visual interactions are written by the generator. Set by hand in Desktop they live only in the on-disk report file, where the next regeneration silently erases them.
- A drift guard protects hand edits. The report generator rebuilds every visual from scratch, so it refuses to run when the file on disk differs from what it would write, unless it is forced. That guard has already caught real Desktop changes that would otherwise have been flattened.
- Validated before it ships. The
powerbi-report-authorCLI checks the generated report; the last build returned 0 errors and 0 warnings.
Caveats, stated plainly
- About 21 of the 50 states publish a partial value in any given year, because of the withholding described above. This report shows the value as published.
- Dollars are nominal and not adjusted for inflation.
- Principal commodities are a Type 1 attribute - the most recent release wins. The text differs between releases for most states, but the differences are USGS copyediting rather than changes in the ground.
- The bigger tables in the USGS release - import reliance, critical minerals, world supply - sit at different grains with no clean shared key. Forcing them into this star would have been dishonest, so they are left out.