โ† Back to portfolio
O2C Reconciliation Modernization
L3 โ€” Complete
Dashboard โ€” DAX Measures + Live HTML Build
L2's SQL output (99,441 reconciled orders, 6 exception types) needed a visual layer. The DAX measures were built and verified in Power BI Desktop first, then the presentation layer was built directly in HTML for this portfolio โ€” both are documented below.
๐Ÿ“‚ Source: l2_exception_classified_dated.csv ๐Ÿ›  Tool: DAX (Power BI) โ†’ Chart.js (HTML) ๐Ÿ“… Completed: Jul 2026 ๐ŸŽฏ Match rate: 98.37%
Contents
01
Building the DAX layer in Power BI
Turning L2's two CSV exports into measures a stakeholder can read at a glance
L2 produced two files: l2_exception_classified_dated.csv (99,441 order-level rows) and l2_exception_summary.csv (6-row severity rollup). On import, Power BI names each table after its source file โ€” including the extension โ€” so the working table name became l2_exception_classified_dated csv, with a space in it. Five measures were built on top of it.
Total Orders = COUNTROWS('l2_exception_classified_dated csv')
Counts every row in the table, exceptions and matches together. This is the denominator every other rate is measured against โ€” expected to equal L0's 99,441.
Matched Orders = 
CALCULATE(
    COUNTROWS('l2_exception_classified_dated csv'),
    'l2_exception_classified_dated csv'[exception_type] = "OK"
)
CALCULATE re-evaluates COUNTROWS under a filter โ€” here, only rows where exception_type = "OK". This is the count of orders that reconciled cleanly with no exception raised.
Match Rate % = 
DIVIDE([Matched Orders], [Total Orders])
DIVIDE is used instead of / because it returns blank instead of an error if Total Orders is ever zero. Must be formatted as Percentage in the measure's Format field, or it displays as 0.98 instead of 98%.
Exception Orders = 
[Total Orders] - [Matched Orders]
Simple subtraction between two already-defined measures โ€” DAX measures can reference each other, so this doesn't need its own CALCULATE.
Total Financial Exposure = 
SUMX(
    FILTER('l2_exception_classified_dated csv', 'l2_exception_classified_dated csv'[exception_type] <> "OK"),
    IF(
        'l2_exception_classified_dated csv'[exception_type] = "CANCELED_WITH_PAYMENT",
        'l2_exception_classified_dated csv'[payment_total],
        ABS('l2_exception_classified_dated csv'[diff])
    )
)
SUMX walks row-by-row through every exception (everything not "OK") and applies an IF to pick the right number per row. For most exception types, exposure is ABS(diff) โ€” the reconciliation gap. CANCELED_WITH_PAYMENT is the one case where diff doesn't apply โ€” there's no order total left to reconcile against โ€” so exposure there is the full payment_total instead: money collected with no matching delivery.
High Severity Orders = 
CALCULATE(
    [Exception Orders],
    'l2_exception_classified_dated csv'[exception_type] = "CANCELED_WITH_PAYMENT"
)
Same CALCULATE pattern as Matched Orders, but filtering to the single highest-severity exception type and reusing the Exception Orders measure instead of writing COUNTROWS again.
โš  Table names with spaces need quotes
Every reference to the table above is wrapped in single quotes: 'l2_exception_classified_dated csv'. Typing the name bare โ€” l2_exception_classified_dated csv[exception_type] โ€” throws Failed to resolve name 'l2_exception_classified_dated', because DAX reads the space as the end of the identifier. Any table or column name containing a space, or starting with a number, needs single quotes around it. Using Power BI's autocomplete when typing formulas avoids this โ€” it inserts the quoted name automatically.
measurevalueverified against
Total Orders99,441L0 row count
Match Rate %98.37%L2 target โ‰ฅ95%
Exception Orders1,618L2 exception_summary sum
Total Financial ExposureR$271,891.01L2 exception_summary sum
High Severity Orders622CANCELED_WITH_PAYMENT count
02
Presentation layer โ€” built in HTML instead of Power BI
Same verified numbers, packaged for this portfolio
With the DAX layer verified against L2's SQL output, the dashboard below was built directly in HTML and Chart.js rather than as a Power BI screenshot or embed โ€” this portfolio already runs as static HTML on GitHub Pages, so the dashboard opens inline, in any browser, with no separate file to download.
โœ“ Same reconciliation logic, different rendering layer
The dashboard below pulls from the same l2_exception_classified_dated.csv and l2_exception_summary.csv exports used to verify the DAX measures above โ€” only the presentation layer changed.
03
Live dashboard โ€” summary + exception breakdown
The same KPI cards and severity chart from the Power BI build, live in this page
Total orders
โ€”
Match rate
โ€”
Exception orders
โ€”
Financial exposure
โ€”
High Medium Low Expected
04
Live dashboard โ€” monthly trend
24 months of exception volume by type, excluding matched (OK) orders
05
Live dashboard โ€” order-level drill-down
All 1,618 exception orders, filterable and sortable
06
Lessons + what this proves
Closing out L3 before moving to L4 โ€” AI use cases
98.37% match rate confirmed at the visualization layer
Actual ยท cross-checked vs L2 SQL output
6 DAX measures written and validated in Power BI
Actual ยท Match Rate %, Total Financial Exposure, etc.
Quoted names โ€” table names with spaces need 'โ€ฆ' in DAX
Actual ยท caught via Failed to resolve name error
1,618 exception orders browsable at row level
Actual ยท filter, search, sort โ€” no server required
๐Ÿ’ก
Lesson: Keeping the SQL layer, the aggregation layer, and the presentation layer independent meant the same L2 exports could drive both the Power BI measures and the HTML dashboard below with identical results โ€” the presentation format can change without touching the underlying reconciliation logic.