Illustrative example. A plant issues purchase orders (POs) at negotiated prices, receives stock, and sends supplier invoices to accounts payable (AP). When a supplier bills above the PO price, AP holds the bill or approves an override so production keeps running. Buyers often discover the overrun weeks later in a month-end export—or mute a daily digest packed with rounding noise and freight fees.
Fixing that gap is a purchase price variance alerts in manufacturing problem. Upstream controls cover clearing OCR errors on an invoice review screen, blocking duplicate supplier invoices in the ERP, and routing AP posting holds through a three-way invoice matching exception queue. Downstream, procurement needs deterministic buyer thresholds, stable PO, invoice, supplier, and item keys, an actionable alert payload and buyer review task, and a business intelligence and analytics star schema.
Using Microsoft’s public Dynamics 365 Finance three-way matching policies, Accounts payable invoice matching overview, and Power BI star schema guidance, this guide specifies an illustrative, made-up workflow—not a real manufacturer, supplier, or Insonl client.
Set buyer price variance thresholds and post-override triggers
AP posting rules and buyer alerts answer different questions. See our three-way invoice matching guide for ERP policy setup, freight checks, and AP hold queues. Per Microsoft’s invoice matching overview and three-way matching policies, two-way or three-way matching policies are set at the legal-entity level and—depending on Allow matching policy override—can be assigned by vendor, item, item-and-vendor combination, or purchase order. Both policies always check net unit price (using tolerances configured separately on the Price tolerances page) and can optionally enable price totals matching (comparing cumulative current, pending, and posted invoice net amounts against the PO line, validating partial billings on the final invoice for that line).
Four rules make buyer alerts actionable:
- Fix the price basis before computing variance: In Dynamics 365 Finance, line
Net amountequals(Unit price * Line quantity) + Line charges - Line discountsandNet unit pricedivides that total by quantity, so allocated line charges can shift ERP net unit price. For buyer purchase price variance (PPV) alerts, calculatepo_net_unit_priceandinvoiced_net_unit_priceafter line discounts and before separately tracked charges, and compute extended variance on the same invoiced quantity on both sides:(invoiced_net_unit_price - po_net_unit_price) * invoiced_quantity. Keep freight and handling separate so carrier surcharges do not trigger unit-price disputes. - Declare percentage and amount gates explicitly: Specify whether an alert requires both thresholds (an AND floor that drops rounding noise on low-value hardware) or either threshold (an OR cap that escalates large dollar hits at small percentages). Scope thresholds by item-and-vendor pair or commodity group rather than one plant-wide rule.
- Alert buyers after AP posting overrides: Exceeding unit-price tolerance triggers a warning icon in Dynamics 365 Finance’s Unit price match column. Where approval is required, an authorized user can still select Approve posting with matching discrepancies on Invoice matching details to post the bill; where not required, posting continues if no other errors block it. Fire a buyer task whenever the line crosses the buyer threshold—even after an AP override—suppressing it only for a pre-approved spot-buy or contract amendment, or a reconciled negative variance.
- Keep threshold math deterministic: Never ask a language model whether a price gap is material.
Join PO, invoice, supplier, and item keys cleanly
Vendor names, free-text descriptions, and bare invoice numbers break joins. Upstream, an invoice review screen clears OCR errors and duplicate supplier invoice detection blocks re-sent bills.
Anchor every PPV check on stable keys:
| Key component | Role in PPV alerting |
|---|---|
legal_entity_id |
Separates plants or subsidiaries reusing PO or supplier numbering. |
po_number + po_line_number |
Identifies contracted price, expected charges, and PO owner. |
erp_invoice_id + invoice_line_number |
Provides a collision-free internal invoice-line key inside the ERP. |
supplier_id + invoice_number (+ scope & document class) |
Displays the vendor reference; pre-posting without an ERP ID, add the supplier’s verified numbering scope (fiscal year or series) and document class (invoice vs. credit memo) so legitimate reuse cannot collide. |
item_id + unit_of_measure + currency_code |
Catches unit-of-measure or currency mismatches before variance math runs. |
Build idempotency_key from (legal_entity_id, po_number, po_line_number, erp_invoice_id, invoice_line_number). Wait for erp_invoice_id before opening the task—or, if you alert pre-posting using supplier numbering scope and document class, map that pre-ERP key onto the permanent key once the ERP ID arrives so the same task updates in place instead of creating a duplicate.
When invoices omit item_id or po_line_number, let a model suggest candidate PO lines for human confirmation upstream so guesses never emit alerts.
Emit an actionable alert payload and buyer review task
“Price mismatch on invoice” forces a buyer across multiple ERP screens. Emit a self-contained payload creating a tracked task for the item_id or commodity buyer (falling back to the PO creator if unassigned).
(Illustrative, made-up example—not a real manufacturer, supplier, or Insonl engagement. Illustrative rule for (SUP-0184, ITEM-4410): alert when both net unit-price variance exceeds 2.0% and extended line variance exceeds $250.00, after line discounts and before separate charges. These thresholds are invented for the example; the ERP’s own price tolerances are configured separately, as described in Microsoft’s three-way matching policies.)
{
"alert_id": "PPV-2026-04182",
"idempotency_key": "PLANT01|PO-2026-4108|2|APINV-0049182|2",
"legal_entity_id": "PLANT01",
"po_number": "PO-2026-4108",
"po_line_number": 2,
"erp_invoice_id": "APINV-0049182",
"supplier_id": "SUP-0184",
"invoice_number": "INV-2026-9034",
"invoice_line_number": 2,
"item_id": "ITEM-4410",
"currency_code": "USD",
"unit_of_measure": "EA",
"invoiced_quantity": 500,
"po_net_unit_price": 12.00,
"invoiced_net_unit_price": 13.08,
"unit_price_variance_pct": 9.0,
"line_variance_amount": 540.00,
"separate_charges_amount": 0.00,
"breached_rules": ["net_unit_price_pct", "line_variance_amount"],
"ap_matching_status": "unit_price_warning_held",
"assigned_buyer_id": "BUYER-14",
"erp_matching_ref": "IMD-2026-9034-02"
}
Here $13.08 versus $12.00 gives +9.0% (> 2.0%) and ($13.08 - $12.00) × 500 = $540.00 (> $250.00), breaching both of the invented thresholds (see Microsoft’s three-way matching policies for how ERP price tolerances are configured separately):
- Keys and reference:
po_number,po_line_number,erp_invoice_id,supplier_id,invoice_number,invoice_line_number,item_id, anderp_matching_refopen the exact matching record. - Price and variance inputs:
invoiced_quantity,po_net_unit_price,invoiced_net_unit_price,unit_price_variance_pct,line_variance_amount, andseparate_charges_amountexpose unit-of-measure errors or price hikes immediately. - Rule and matching/posting state:
breached_rulesnames triggered rules;ap_matching_statusrecords matching or posting state (held vs. posted under override). If recovery depends on whether cash has left, add a separate payment-status field.
Track tasks through open, in_review, awaiting_supplier, and resolved, requiring a structured disposition on close:
request_credit_memo: supplier overbilled; link the vendor credit memo once posted.update_price_agreement: valid increase; update the ERP price record so future bills stop alerting.accept_one_time_variance: approved spot premium; log reason and approver.dispute_invoice: active pricing dispute; if the invoice is unposted, maintain the AP posting hold, and if it has already posted, route the dispute to AP for follow-up.reroute_to_ap_coding: unit-of-measure, currency, or charge-code entry error.
Keep AI bounded to drafting a supplier note from the verified payload or summarizing prior dispositions for (supplier_id, item_id); deterministic code owns routing, escalation, state transitions, and ERP write-back.
Model variance history in a Power BI star schema
To spot repeat supplier creep, verify recovery, and tune thresholds, load variance events, dispositions, and linked credit memos into a business intelligence and analytics semantic model. Six rules from Microsoft’s Power BI star schema guidance prevent broken metrics:
- Stage through a data warehouse and ETL: Power BI depends on Power Query to import and transform data. Microsoft recommends building a data warehouse and ETL pipeline first for large volumes or slowly changing dimensions (SCD), then connecting Power BI to it.
- Hold one consistent fact grain: Load
FactPurchasePriceVarianceat one grain—one row per evaluated invoice line matched to a PO line—with dimension keys and additive measures (InvoicedQuantity,POExtendedAmount,InvoicedExtendedAmount,PriceVarianceAmount), placing descriptive fields in dimension tables linked one-to-many. Track actual credit recovery from linked credit-memo amounts; a closedrequest_credit_memodisposition records only the buyer’s decision, not credit posted. - Add surrogate keys for composite ERP keys: Power BI relationships link one unique column on the “one” side to one column on the “many” side. Because ERPs key suppliers and items by composite columns (legal entity plus code) or versioned rows, add integer surrogate keys in the warehouse (or a Power Query index column merged into the fact query on smaller models).
- Hide unit-price columns and guard DAX measures: Microsoft warns that per-unit rates like
Unit Pricemust never be summed—hide raw unit-price columns and write explicit DAX measures. Sum monetary amounts only within one currency or after converting to a reporting currency, and compute quantity-weighted unit prices only across comparable items and consistent units of measure. Explicit measures are also required for Analyze in Excel (MDX queries) and Power BI paginated reports using the MDX query designer; because Power BI Desktop live connections let authors unhide fields, document approved measures clearly. - Apply SCD Type 1 and Type 2 deliberately: Use Type 1 SCD (non-incremental refresh overwrite) for supplementary fields like supplier contacts. Use Type 2 SCD in the warehouse—with a surrogate key,
StartDate,EndDate(empty or12/31/9999for current rows), and optionallyIsCurrent—when historical attribution matters, such as a buyer rotating commodities. Power Query cannot detect changes and run time-based lookups for SCD Type 2 alone, though Power BI can load a pre-loaded SCD Type 2 table; include both a member column and an unambiguous version label (such asBuyer (Start–End)). Store rapidly changing prices as fact measures. - Flatten hierarchies, split date roles, and pack status flags: Combine item category, subcategory, and item into one denormalized
DimItemtable rather than a snowflake for shorter filter propagation and single-table hierarchies. Create separate role-playing date tables (DimOrderDate,DimInvoiceDate,DimResolutionDate) with self-describing columns (Order Year,Invoice Year) and table descriptions so users filter order and invoice dates simultaneously withoutUSERELATIONSHIPsprawl. Pack low-cardinality flags (matching_policy,breached_rule_group,task_status,disposition_code) into one junk dimension (DimVarianceFlags) with a surrogate key, and keeppo_numberon the fact table as a degenerate dimension—or move paired order and line numbers into a dedicated degenerate dimension table when filtered together.
Next step
Before building alert rules or dashboards, map how POs, receipts, charges, and invoices move through your ERP and where overrides hide price drift—the groundwork behind Insonl’s business intelligence and analytics practice. Start with Insonl’s free AI workflow audit for a written workflow map, recommendations, and a fixed-scope estimate. The audit is free; follow-on work is optional, and Insonl charges only for work after the audit.
Sources
- Microsoft Learn (updated 4 Jun 2026), Three-way matching policies — Finance | Dynamics 365: https://learn.microsoft.com/en-us/dynamics365/finance/accounts-payable/three-way-matching-policies
- Microsoft Learn (updated 15 May 2025), Accounts payable invoice matching overview — Finance | Dynamics 365: https://learn.microsoft.com/en-us/dynamics365/finance/accounts-payable/accounts-payable-invoice-matching
- Microsoft Learn (updated 30 Dec 2024), Understand star schema and the importance for Power BI: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
- Insonl, Business Intelligence and Analytics: https://insonl.com/services/enterprise/business-intelligence-analytics
- Insonl, Three-Way Invoice Matching Exceptions: Queue Rules: https://insonl.com/journal/three-way-invoice-matching-exception-queue
- Insonl, Duplicate Supplier Invoice Detection in ERP: https://insonl.com/journal/duplicate-supplier-invoice-detection-erp
- Insonl, What an Invoice Review Screen Should Show Before Approval: https://insonl.com/journal/invoice-review-screen-before-approval
- Insonl, Free AI Workflow Audit: https://insonl.com/ai
