Case study

FAR/DFARS Compliance Dashboard

A three-page Power BI report that answers one compliance question — are the FAR and DFARS clauses actually reaching our suppliers, and which supplier do I call today — built as a compliance layer over the systems an aerospace and defense company already has, with every threshold written down before it was coloured.

Role
Sole developer
Organization
Personal project
Status
Shipped
  • Power BI
  • DAX
  • Power Query
  • Data modeling
  • Dashboard design
  • KPI design
  • Data visualization
  • Python
  • GRC
  • Control management
Walkthrough

Screens

  • Compliance Overview page subtitled "FAR / DFARS flow-down, SAM, certificates, findings, small business and CMMC across the open PO book. As of 5 Sep 2026 · illustrative data." Six KPI cards read flow-down coverage 93.8 percent (1,865 of 1,988 required clauses), SAM registrations active 96.8 percent (5 lapsed of 158 suppliers), 66 certificates expiring in 90 days (11 within 14 days, 5 expired), 7 open compliance findings (down 9 versus last quarter, oldest 61 days), small-business attainment 32.8 percent (above the plan goal of 25 percent) and CMMC at required level 70.0 percent (35 of 50 CDI-handling suppliers). A line chart tracks open findings at each month end from October 2025 to September 2026 split into internal audit, self-assessment and DCMA or customer sources. A red bar chart ranks the eight lowest compliance scores, Kestrel Composites lowest at 15. Below, a column chart compares small-business attainment against plan goal across six categories, a bar chart ranks required clauses missing from PO terms with 52.204-25 highest at 41 PO rows, and a table lists certificates expiring within 90 days with lapsed ones shown as negative days, down to minus 140.
    Page 1 is triage. The subtitle carries the as-of date and states "illustrative data" on the page itself — every supplier, CAGE code, program, buyer and figure in this report is fabricated.
  • PO Flow-Down page titled "which open POs are missing required clauses" and subtitled "One row per open PO with a required clause missing from its terms. Y = included, N = required but missing, – = not required for this PO." Four cards read 98 POs with a flow-down gap (of 412 open POs, $56.4M exposure), most-missed clause 52.204-25 (Section 889, 41 POs), buyer with the most gaps D. Okafor (40 gaps across 29 POs) and 3 POs over the TINA threshold (all 3 have certified cost data on file). Below them a flow-down matrix lists open POs with a gap, largest PO value first, giving PO number, supplier, buyer, PO value and threshold band, then one column for each of nine tracked clauses marked green Y, red N or a dash for not required, and a final count of missing clauses. The top row is PO-26-0412 to Kestrel Composites, buyer S. Nguyen, $3,120,000, Over TINA, 2 clauses missing.
    The matrix is the working document — one row per PO with a gap, nine clause columns, sorted by dollar value so the largest exposure is the first thing read. The dash matters as much as the N; a clause that was never required is not a failure.
  • Supplier Profile page subtitled "Pick a supplier to see SAM, CMMC, certificates, flow-down and delivery performance in one place", with the supplier slicer set to Meridian Precision Machining, Inc. A header card gives CAGE 7XK42, UEI LM3QK8N7T2J9, machined structures, Escondido CA, beside cards reading compliance score 40 in red labelled Critical, open PO value $2M across 4 programs and 6 open POs, and flow-down coverage 94.3 percent, 33 of 35 required clauses. A table of compliance checks and documents on file colours each status — SAM registration expires in 14 days, exclusion check clear, SPRS assessment below 110 at 78 of 110, CMMC certificate a gap with level 1 held against level 2 required, Section 889 representation and ITAR registration current. A bar chart shows days to expiry by document from SAM registration at 9 days up to Section 889 at 297. Four cards read on-time delivery 88.5 percent, 3 quality rejections in 12 months, 2 open SCARs and composite grade B at 86.1. A panel headed "Why this score" breaks the 40 into SAM 15, CMMC 10, certificates 0 and flow-down 15 out of 25 each, above the bands under 50 Critical, 50-64 Action needed, 65-84 Watch, 85+ Good.
    The score is never shown without its arithmetic. A supplier scoring 40 is not a red dot — it is SAM 15, CMMC 10, certificates 0, flow-down 15, and the zero is the thing to act on.

In one minute

A Power BI report built around the question a supply-chain compliance lead asks first: are the FAR and DFARS clauses actually flowing down to our suppliers, and which supplier do I call today? It is a compliance layer over the data an aerospace and defense company already holds — ERP purchase orders, the vendor master, SAM.gov, SPRS, certificate files, audit findings — and it replaces none of them. The data is fabricated. The model, the measure logic and the threshold discipline are the real work.

The problem

  • Flow-down compliance is a join nobody owns. Whether a clause reached a PO depends on the prime contract’s clause set, the PO’s dollar threshold band and the PO’s actual terms — three facts that live in three places, so the question is usually answered by reading POs.
  • “Compliant” is not one number. A supplier can be current in SAM, short a CMMC level, and carrying an expired certificate at the same time, and each of those is a different phone call to a different owner.
  • A red cell is worthless if nobody can say what turned it red.

What I built

  • Three pages, three questions, in escalating specificity. Page 1 is triage across the whole open PO book. Page 2 narrows to the POs with an actual gap. Page 3 is one supplier, every check. Nothing appears on an earlier page that the later page cannot explain.
  • A star schema over one workbook. 14 tables and 8 relationships, all many-to-one and single-direction, from a 13-sheet synthetic workbook. PO Clauses is the grain that makes the report possible: one row per open PO per tracked clause, carrying Required (derived from the prime contract’s clause set and the PO’s threshold band) and Included (what the PO terms actually say). Coverage is then just one over the other.
  • 110 measures in one _Measures table, grouped into ten display folders — SAM, CMMC, Certificates, Findings, Flow-down, Flow-down matrix, Score, Small business, Supplier profile, Settings — so the model is navigable rather than a flat list.
  • Nothing hard-coded that a regulation could change. The as-of date and the FAR dollar thresholds (SAT, TINA) live on a Settings sheet and are read as data. The workbook path is a single Power Query parameter, so relocating the folder is a one-value edit.
  • A scoring rule that shows its work. The compliance score is 25 points each for SAM, CMMC, certificates and flow-down, with the deduction for each stated in the model — and the supplier page prints the four components next to the total rather than only the colour.
  • A build that is text all the way down. Saved as a .pbip: the semantic model is TMDL and the report is PBIR, both diffable in git and editable without opening Desktop. The workbook, the model and the 70 report objects are each generated by a seeded Python script, so the whole artifact rebuilds deterministically.

Rules the whole system enforces

  • A threshold is written down before it is coloured. Certificate status, score bands (under 50 Critical, 50–64 Action needed, 65–84 Watch, 85+ Good) and every RAG cut are defined in the repo, not in a visual’s formatting pane.
  • Colour comes from a measure, not from a hand-formatted cell.
  • The page states the question it answers in its own title, so a screenshot is self-describing.
  • A number that means “not applicable” is shown as a dash, never as a zero or a failure — the flow-down matrix distinguishes required but missing from never required.
  • Illustrative data says so on the page, next to the as-of date, not only in the README.

Outcome

  • Every headline figure reconciles to a KPI Summary table the data generator writes from the same rows, and the reconciliation was run before shipping: coverage 93.8% (1,865 of 1,988), 98 POs with a gap against $56.4M of exposure, SAM active 96.8% (5 lapsed of 158), 66 certificates expiring within 90 days (11 within 14 days, 5 already expired), 7 open findings (down 9 on the quarter, oldest 61 days), small business 32.8% against a 25% goal, CMMC at required level 70% (35 of 50), most-missed clause 52.204-25 at 41 PO rows, D. Okafor the buyer with the most gaps at 40, 3 POs over the TINA threshold, lowest supplier score 15.
  • Five DAX and PBIR failures are written up in the repo rather than quietly fixed, including the two worth remembering: a column cannot sort by a calculated column derived from itself, and a measure that never returns blank cross-joins every table it touches unless it is anchored to a fact-table column.
  • No time-saved, cost-avoided or accuracy figure is claimed. The data is illustrative and nothing in the report measures one.

Stack

Power BI Desktop .pbip project · TMDL semantic model (14 tables, 8 relationships, 110 measures) · PBIR report definition (3 pages, 70 visuals, a generated JSON theme) · Power Query (M) over a 13-sheet Excel workbook · DAX · Python generators for the workbook, the model and the report.

Back to selected work