Model and Measures

This reference describes the existing report's inputs and measures. It imposes no compatibility requirements on the commercial services design. See the implementation reference for source locations and access.

Use this page to trace a Power BI field to its inputs and exact DAX formula. For the meaning of each Bridge row, its sign and the Xero account-code groups, see Business profit or loss. The runnable model is pbi/commercial-margin/CommercialMargin.pbip.

From source systems to the report

app/commercial_margin/report.py reads July-2026-onward Xero sales invoices, sales credits and monthly accrual P&Ls, plus the Xero chart of accounts. It reads current CRM Companies, Products, Invoices and invoice subforms, MSP Plans, MSP Invoice Lines, Deals and Delivery Models, and Projects task metadata and recorded time. Only financial transactions and time are restricted to the July-onward window; identity/cost metadata is current.
app/commercial_margin/engine.py resolves service identities, matches product costs, attributes recorded time, and builds service-month, business-month, bridge and issue rows. products.py, labour.py and finance.py supply the product matching, timesheet mapping and P&L classification rules.
/commercial-margin/contribution-report supplies one versioned JSON snapshot using existing reporting Basic authentication. Sources are read-only. Completed snapshots are cached for 15 minutes; HTTP 202 indicates a build in progress, while failed sources produce an error rather than partial data.
Power Query's ContributionSnapshot loads that response from ReportBaseUrl (default https://pepit.fxbn.dev). A nonempty SnapshotPath loads a local JSON file instead. It checks schema version 2 and start date 2026-07-01; imported tables have explicit columns/types and fail on missing required fields.
Power BI relationships apply filters. The DAX measures below aggregate the imported rows. Bridge/account classification is done in the backend, not DAX.

The default snapshot end date is the current date in Australia/Sydney. A partial month's P&L and transactions stop at the requested end date. The current-cost Product price is not a historical supplier price snapshot. Neither report refresh nor a Power BI slicer change creates a Deal or writes Xero.

Confirmed-service browser refresh

review_refresh.py manages the authenticated browser GET/POST endpoint and the commercial-margin-refresh CLI command. scoped_sources.py reads Review_State before selecting customer documents and project work. source_cache.py saves source inputs and incremental CRM indexes separately from calculated snapshots. The browser supports all confirmed services or an explicit confirmed ID subset; recalculation reuses the saved sources for that exact scope and date.

The browser is a draft modelling surface. It passes allow_archived_source_drift=True to the calculation engine: changed archived fingerprints retain their stable line/service link and use current Xero values. Each affected financial line adds one Archived source drift issue, summarized as source_warning_count in the browser response. Customer checks, missing-source coverage guards, native invoice reconciliation and subtotal validation still run. The engine default and full-business export retain strict fingerprint validation.

Completed source collection is saved as .collected-inputs.json before modelling. On success, it becomes the published .inputs.json alongside the snapshot; on failure it remains available for recalculation. Published inputs continue to supply the old snapshot's catalogue. Source signatures use mapping/policy/archive content; model revisions and staff inputs have a separate calculation signature. Changing a calculation version can therefore reuse recent collected inputs. An error stores the failed stage and model signature. Reads do not retry an unchanged failure after a timer; explicit refresh/recalculate or a changed model signature can retry. Raw exception text stays in server logs.

GET .../service-review-report?poll=1 returns a small 202 progress response while the scope lock is held, without reading the snapshot or building service detail. On completion it returns the report or failure. The browser allows 30 seconds for response headers, then 90 seconds for the JSON body. Transport failures and HTTP 502/504 responses trigger at most two recovery GETs, after two and four seconds; POST requests are never replayed. Explicit report errors do not trigger recovery. Source collection runs independently in a background thread. Deploy/reloader process exits interrupt that thread, so persistent inputs and reports aid recovery but do not make the running job durable.

service_review.py derives monthly_activity on each returned service from the same snapshot and costing rules. A monthly row has period boundaries and partial status; revenue, hours, labour/other/total costs, recurring expectations and actual contribution; selected Product components and invoice-item evidence; and a cumulative activity subtotal. Fixed-scope budget totals remain on the service. delivery_reserve.py adds opening_reserve, closing_reserve, reserve_movement, adjusted_margin and adjusted_margin_percent to each month and its cumulative subtotal. The existing service totals are the rounding controls for component/month amounts.

contribution.py adds contribution_cost, contribution_provision, contribution, contribution_percent, contribution_basis and contribution_note to the service, months and cumulative subtotals. The browser aggregates these additive cost flows for its headline, keeping actual_margin as incurred facts. Project, Onboarding and Recurring agreement roles use reserve movement; an open fixed project with unknown openings and a complete plan uses a labelled proportional cost estimate, bounded below by known cumulative incurred costs. It does not populate missing reserve/hour balances. Tests: test_provisioned_contribution.py.

commercial_margin.html hosts Commercial services, Performance and Forecast panels, with a separate locked-service drill-down. commercial_margin_model.js centralises date ranges, null-preserving totals, weighted rates, complete-month comparisons, whole-service role/customer filtering and sortable service lists. commercial_margin_dashboard.js renders the overview, trend, financial matrix, separate forecast horizons and shared navigation. Existing refresh endpoints and saved-source behavior are reused; display filters do not issue source requests.

The review catalogue retains Service_Role, Company, source contact identity and lifecycle. commercial_services/service_roles.py produces service_role, source_service_role, customer_id, customer_name and lifecycle on every service result. It aliases Hardware sale for display and retains custom or unresolved roles. It never derives the role from business category, recurrence or Products. Restoring a catalogue from saved raw inputs also restores these metadata fields without fetching source systems.

URL state includes page, role, customer, list search/attention/sort, recorded period/mode/month, salary preference and the selected service. detail selects Summary, Activity or Lines & evidence; detail dates remain independent after opening. The parent page stays available for the return link. Browser history also retains scroll positions. Old service URLs still open the same service with the default parent and section.

commercial_margin_detail.html renders the service forecast, configured lines, cost spread, activity, components, invoice items and snapshot-scope budgets in separate sections. Cumulative activity recomputes from the selected start. Missing rows remain unavailable. Date and section changes do not re-evaluate budgets or contact source systems. Tests cover browser arithmetic, role filters, coverage and detail rendering in tests/test_commercial_margin_dashboard.py, tests/js/commercial_margin_dashboard.cjs and tests/commercial_services/test_service_roles.py.

forecast_inputs.py validates and atomically stores per-service management inputs in COMMERCIAL_MARGIN_CACHE_DIR/forecast-inputs.json (owner-only file). Back up this file: it contains reviewed inputs, not disposable source cache. A global version and file lock prevent concurrent lost updates. The authenticated POST /commercial-margin/forecast-inputs/<service_id> accepts JSON with version and inputs, requiring X-Requested-With: CommercialMargin; no cross-origin access is enabled. Inputs are agreed_value, opening_hours, opening_other_cost, and dated estimates containing as_of, remaining_hours and remaining_other_cost. Optional allowance_policy accepts carry_forward or expire_monthly; the latter explicitly selects expiry for Project/Onboarding/Recurring agreement allowances. Other roles retain expiry. Null amounts mean unknown; zero is explicit. Successful saves are used by the next report read without a source refresh. Estimates are end-of-day positions and later work/purchases consume them.

Whole-service fields include forecast_actual_cost, forecast_total_cost, forecast_margin, agreed_value, delivery_reserve, forecast_hours, forecast_remaining_hours and forecast_completed. They are separate from invoice-based scope margin and monthly adjusted_margin. Private forecast inputs load only for authenticated/private reviews, or through an explicit argument for offline calculations. Tests: test_delivery_reserve.py, test_forecast_inputs.py and the dashboard browser tests.

The browser retains Product grouping and report-period Seat allocation. These presentation changes do not change the Power BI snapshot contract below.

For the single-labour-line Consulting pilot, labour_model = Consulting identifies the work-costing rule, alongside any purchased components. Products select the calculation independently of Plan presence or service category. Explicit Labour_Budget_Type uses Expected_Labour_Hours; a blank type retains the legacy Quantity/Arrangement convention. Recurring allowance derives monthly_allowance_hours from the applicable hours field and an allowance_end_date from an ended service's End Date. The same dated proration is used for service and monthly expectations. Missing monthly hours propagate null expectations; actual Consulting work uses the line tier rate for recorded hours. An explicit fixed line with blank hours reports pending expected/projected cost. Purchased-line costs remain visible; null costs propagate into complete service totals. Selling Quantity and Unit Price Override do not establish that labour budget. The explicit MSP-DISCOUNT and MSP-NFP-DISCOUNT Product codes have zero billed cost on both axes; ordinary Product credits retain the signed billed-cost rule.

CRM modified-since checkpoints use ISO timestamps without fractional seconds and a 60-second overlap. A complete index read runs at least daily to remove deleted records. Invoice subform caches include their parent modification timestamp and expire daily. Xero history is scoped by customer plus known document identities; complete shared documents and archived ownership guards remain intact. Projects use per-project task pagination and per-project monthly user/task/day reports. The shared timelog parser and attribution precedence preserve existing hours.

The collector keeps shared CRM identity indexes to resolve competing work scopes and invoice ownership correctly. It avoids unrelated invoice subforms, Quote items, Projects tasks/time and business P&Ls. Collection stage offsets and cache hit/miss counts are recorded in the snapshot and server completion log. Each source refresh is bounded to four concurrent reads; a filesystem lock serializes source jobs across processes. Workers publish only after calculation and display validation. File writes are atomic with owner-only permissions.

API references: CRM Get Records, Xero invoice filtering.

Tables, grain and relationships

Power BI table	JSON array	One row represents	Relationship keys / source
`Months`	`months`	One calendar month from July 2026 through the snapshot end date	Unique `month_start`; contains `month_label` and `period_status`.
`Services`	`services`	One commercial or labour reporting identity	Unique `service_id`; includes company, service type, scope and review status.
`Contribution`	`service_months`	One service in one month	`month_start` → Months; `service_id` → Services. Built from Invoices and Labour.
`Invoices`	`invoice_lines`	One retained Xero invoice or credit-note line	Same two dimension keys; document and line IDs preserve traceability.
`Labour`	`labour`	One mapped project/task/person/work-date time row	Same two dimension keys; includes unresolved and nonbillable work.
`Issues`	`issues`	One source and issue type	Same two dimension keys; warnings may overlap.
`Business`	`business_months`	One whole-business month	`month_start` → Months only; Xero P&L totals and invoice reconciliation.
`Accounts`	`pnl_accounts`	One P&L leaf account in one month	`month_start` → Months only; includes income and expenses.
`Bridge`	`bridge`	One of 11 bridge steps in one month	`month_start` → Months only; signed `profit_effect`, ordered by `step_order`.
`Staff`	`staff_months`	One configured or observed person in one month, including active employees without time	`month_start` → Months only; no service relationship.
`Metrics`	None	Calculated helper table that hosts 45 measures	No relationships. Its hidden `Metric` column comes from a one-row DAX `DATATABLE`.

Filters flow one way, from each dimension to its facts. There are no fact-to-fact or bidirectional relationships. Therefore choosing an invoice row does not automatically filter labour rows; use the shared service and month dimensions to examine both. The Service detail drillthrough uses Services[service_id] to distinguish similarly named services.

Metrics intentionally retains a calculated partition and its original table and helper-column identities. Changing that partition to Power Query can fail when Power BI loads a cached model. The helper row has no financial meaning.

Default page filters

All five pages initially select Months[period_status] = "Complete month". A complete calendar month does not establish that Xero's books are closed.

Service contribution initially includes MSP contract, Recurring contract, Project / sale and Reporting only services. Central and unassigned labour remain in the model but are excluded by this initial service-type selection. Its labour-capacity visual additionally selects service_scope = "Contract".
Business profit or loss is for the selected months across the business. Business, Accounts and Bridge have no relationship to Services, so company/service/type filters cannot allocate or change their results.
Staff cost reconciliation compares all staff with the whole-business payroll for the selected months. It has no client/service slicer or service relationship.
Reconciliation and Service detail use shared service/month dimensions to inspect the relevant warning, invoice and time rows.

Imported calculation fields

Invoice and product fields

Posted AUTHORISED and PAID AUD documents in the selected date window are eligible. Ex-GST line amounts must tie to each document subtotal within $0.02.

Field	Calculation / interpretation
`document_amount`	Ex-GST Xero line amount, with a negative sign for sales credits. Inclusive-tax lines subtract their TaxAmount.
`included_in_revenue`	True when the line's Xero account type is `REVENUE`, `SALES` or `OTHERINCOME`.
`revenue`	`document_amount` for included lines; zero otherwise. Non-income-coded lines remain traceable and generate accounting-classification review when monetary.
`quantity`	Xero quantity, signed negative on credit notes. CRM quantity does not replace it.
`product_cost`	Current matched Product Cost_Price × Xero quantity where required and known, or an explicit reviewed cost adjustment. Unresolved required costs are zero in this numeric field and separately flagged.
`unknown_cost_lines`	1 for a line with a required but unresolved product cost, otherwise 0. A sales credit does not establish a supplier-cost reversal.
`unreviewed_lines`	1 for included revenue on an unreviewed reporting service, otherwise 0.
`product_match_basis`, `cost_basis`, `mapping_basis`	Explain how product identity, costing and service attribution were established. They are separate checks.

Product matching tries a reviewed product ID, a unique exact CRM invoice-line match, a unique exact MSP Invoice Line match, a specific Xero ItemCode, then a unique exact normalised product label. An ambiguous result at a matching stage is retained as ambiguous rather than resolved by choosing an arbitrary product. Generic 003 is only a service-code fallback under the explicit service rules; it does not establish a hardware or licence product. Required-cost detection uses the account/category and recognised supplier descriptions; a resolved service product is not automatically proof that all supplier costs are known. See the calculation approach for the product-cost and credit-review policy.

Labour and service-month fields

Labour[hours] = log_minutes / 60; labour_cost = log_minutes × hourly_cost / 60. hourly_cost is resolved by explicit projects_user_id and work date using the private staff configuration. For salary-only employees it is actual annual salary / (26 × fortnightly paid hours), unless a reviewed costing_annual_salary replaces the numerator for service costing only. The backend retains a separate salary_cost on labour rows, calculated from actual salary, for the staff comparison; Power BI reads that through Staff[salary_allocated]. For contractors it is the configured hourly rate. Missing identity or date coverage uses $80. employee_id groups multiple verified Projects IDs for one person; cost_basis records which rule was used and rate_review flags missing coverage when a file is configured. Without a configured file all time retains the $80 fallback and the snapshot's staff_costing_configured is false.

nonbillable_hours is already part of hours, not an additional cost. unassigned_hours equals hours only when no scope or reviewed project mapping resolves the work. A reviewed business-level assignment is central labour. Power BI capacity formulas remain an explicitly labelled $80/hour scenario; they are independent of the person-specific contribution rates.

Contribution sums revenue/product costs from invoice lines and hours/labour costs from labour rows by service_id and month_start. It also sums the line-quality counters and unassigned hours. Active MSP Plans get service-month rows, including zero-activity months within their billing dates.

Stored field	Backend calculation for one service-month
`known_contribution`	`revenue − product_cost − labour_cost`.
`contribution`	Null if `unknown_cost_lines > 0`, `unreviewed_lines > 0`, or positive revenue has zero recorded hours; otherwise known_contribution. Unassigned labour alone does not make this field null.
`labour_capacity_hours`	`(revenue − product_cost) / 80`, or null when product costs are unresolved.
`cost_status`	Incomplete product costs if unknown_cost_lines > 0, otherwise Known direct costs.
`scope_status`	Review service link if unreviewed lines or unassigned hours exist; otherwise Linked. Revenue without hours and client delivery without revenue replace this status with the applicable coverage warning.
`Services[monthly_support_budget_hours]`	MSP Plan Expected_Support_Hours_per_Month. It is source metadata, not actual hours or the input to Affordable labour hours.

Power BI's Modelled contribution measure recalculates its conditions on the selected totals; it does not sum the stored Contribution[contribution] column. A service-month with revenue and zero hours can have a null stored contribution, while a multi-month selection containing other recorded hours produces a nonblank DAX result. The warning remains in Issues. Inspect the monthly detail and review queue before interpreting aggregate coverage.

Business, Accounts and Bridge fields

Accounts groups each monthly Xero P&L leaf account once. Business holds the group totals (payroll, contractors, shared_costs, supplier_costs, other_overhead), P&L xero_revenue, included invoice_revenue, their signed revenue_difference, and Xero's own net_profit control value. Business[unassigned_hours] is the whole-business recorded unassigned time.

The bridge row dictionary and account classification rules define every step and the reconciliation checks. Use Accounts[profit_effect] for the signed impact on profit; Accounts[amount] retains the P&L presentation sign. The report does not import a supplier-bill or payroll-transaction ledger.

Staff monthly fields

The salary and time rules explain the distinction between a salary budget, recorded cost and actual payroll.

`Staff` field	Calculation / interpretation
`employee_id`, `person`, `projects_user_id`	Private stable identity, display name and explicit Projects API IDs. Unknown people keep an `UNREGISTERED` identity.
`recorded_hours`, `recorded_cost`	Sums of all that person's timelog hours and labour cost, including contractor/fallback rows.
`salary_estimate`	Annual salary / 12, calendar-day prorated for covered dates in the month. Null for a contractor or person without a salary period.
`nominal_paid_hours`	Fortnightly hours × 26 / 12, prorated over the same salary dates. Null without salary coverage.
`salary_allocated`	Recorded employee time at the actual salary-only rate, ignoring any approved service-costing salary assumption.
`salary_unallocated`	Rounded salary_estimate minus salary_allocated. Null without salary coverage. Can be negative.
`hours_gap`	Nominal monthly paid hours minus recorded hours costed on the Salary only basis. Can be negative.
`client_hours`, `central_hours`, `unassigned_hours`	Mutually exclusive splits of recorded hours by service classification.
`fallback_hours`	Hours with unresolved rates, or all hours when no private file is configured.
`status`	Salary-only monthly equivalent, contractor estimate, missing-rate review, no recorded time, or hours exceeding the monthly equivalent.

Business[staff_salary_estimate] sums the monthly staff salary budgets; Business[payroll_salary_difference] = payroll − staff_salary_estimate. Both are null when the private file is not configured. They are diagnostics; they do not add expenses to the P&L bridge. Cash paid, employer super, leave balances and individual payroll transactions are not imported.

Review fields

Issues[amount_at_risk] is the absolute value of the amount attached to that warning. Product/scope/timing warnings can refer to invoice revenue, whereas labour-rate, labour-link or revenue-coverage warnings can refer to modelled labour cost. Warnings can overlap. Their total is not a monetary provision, a sum of unknown costs or an estimate of lost profit.

Power BI measure dictionary

These are the 45 explicit measures hosted in Metrics. Formulas are the DAX in scripts/build_contribution_power_bi.py (MEASURES) and the generated CommercialMargin.SemanticModel/definition/tables/Metrics.tmdl. Bracketed names refer to other measures; Table[column] refers to imported data. The formulas operate in the current filter context described above.

Service contribution and capacity

Revenue — Included ex-GST sales invoice revenue less sales credits, for the selected services and months.

SUM(Contribution[revenue])

Product cost — Identified current CRM product costs and reviewed cost adjustments. Unknown product costs contribute zero to this sum and remain flagged; zero here does not prove there is no cost.

SUM(Contribution[product_cost])

Recorded labour cost — Recorded time at each person’s work-date salary or contractor rate, including nonbillable time. Unknown rate coverage uses the explicit $80 fallback.

SUM(Contribution[labour_cost])

Recorded hours — Recorded delivery time, including nonbillable hours. This is not paid staff capacity.

SUM(Contribution[hours])

Known contribution — Revenue less identified product cost and recorded labour. Missing costs or time can overstate this result. Shared costs and actual payroll are not allocated into it.

[Revenue] - [Product cost] - [Recorded labour cost]

Missing cost lines — Number of invoice/credit lines whose required product cost is unresolved, not a dollar estimate of the missing cost.

SUM(Contribution[unknown_cost_lines])

Unreviewed invoice lines — Number of included revenue lines assigned to a service whose scope is unreviewed. This is not the total number of all reconciliation warnings.

SUM(Contribution[unreviewed_lines])

Unassigned hours — Recorded hours retained without a resolved commercial-service or business-level assignment. A service-type filter that excludes unassigned labour also excludes those hours from this measure.

SUM(Contribution[unassigned_hours])

Modelled contribution — Returns known contribution only when selected totals have no missing-cost or unreviewed lines, and positive revenue is not accompanied by zero total recorded hours. Otherwise returns BLANK. This is a coverage check, not proof of complete costs.

IF([Missing cost lines] + [Unreviewed invoice lines] = 0 && NOT ([Recorded hours] = 0 && [Revenue] > 0), [Known contribution])

Modelled contribution % — Modelled contribution divided by included revenue. DIVIDE returns BLANK when the denominator is zero or blank.

DIVIDE([Modelled contribution], [Revenue])

Affordable labour hours — Revenue less identified product cost, divided by $80, before shared infrastructure, overhead or profit. Blank when selected lines have missing product costs. It does not independently test invoice attribution or all review warnings.

IF([Missing cost lines] = 0, DIVIDE([Revenue] - [Product cost], 80))

Labour headroom hours — Affordable hours less recorded hours. Negative means recorded labour exceeds that allowance. Blank when affordable hours is blank.

IF(NOT ISBLANK([Affordable labour hours]), [Affordable labour hours] - [Recorded hours])

Selected months — Count of selected calendar months, including months without activity for a particular service.

DISTINCTCOUNT(Months[month_start])

Average monthly revenue — Selected revenue divided by selected months. This is an invoiced average, not contracted recurring revenue.

DIVIDE([Revenue], [Selected months])

Average monthly contribution — Selected modelled contribution divided by selected months. It recalculates coverage across the selection; it does not average only nonblank service-month contributions.

DIVIDE([Modelled contribution], [Selected months])

Average monthly hours — Selected recorded hours divided by selected months, including months with no recorded activity.

DIVIDE([Recorded hours], [Selected months])

Business result and bridge

Xero profit or loss — Sum of the actual monthly Net Profit control totals from Xero. It is independent of client/service filters.

SUM(Business[net_profit])

Xero total revenue — Xero P&L Revenue plus Other income, for selected months across the business.

SUM(Business[xero_revenue])

Actual payroll — P&L amounts classified as Payroll by the deployed account-code configuration. Normally positive in this card; deducted in the bridge.

SUM(Business[payroll])

Actual contractors — P&L amounts classified as Contractors. Normally positive in this card; deducted in the bridge.

SUM(Business[contractors])

Shared infrastructure and tools — P&L amounts classified into shared costs. Kept at business level; no contract usage allocation is made.

SUM(Business[shared_costs])

Other overhead — Remaining P&L Expenses amounts after payroll, contractor and shared-account classification.

SUM(Business[other_overhead])

Supplier direct costs — Remaining P&L Cost of Sales amounts after payroll, contractor and shared-account classification. These are accounting costs, not Product cost.

SUM(Business[supplier_costs])

Accounting revenue difference — Xero P&L revenue plus other income minus included invoice revenue. This is the signed net difference, not transaction-level journal attribution.

SUM(Business[revenue_difference])

Bridge amount — Sum of signed bridge effects for the selected months and steps. On one step it shows that adjustment; with every step included it reconciles to net profit. It is not a cumulative running-total measure.

SUM(Bridge[profit_effect])

Account cost — Sum of the original signed P&L account amounts in the current context. Despite the name, the Accounts table also contains income. Summing this measure over all income and expense accounts does not calculate net profit; use profit_effect or Xero profit or loss.

SUM(Accounts[amount])

Profit impact — Signed accounting impact used by the "Actual income and expenses" table. Normal income is positive and expenses negative; revenue reductions and expense reversals retain their opposite effects. With all accounts included, the total reconciles to Xero net profit/loss for the selected months. Account filters restrict the total to the selected accounts. The original Account cost measure and source amounts remain unchanged.

SUM(Accounts[profit_effect])

Invoice and labour detail

Invoice revenue — Included revenue summed directly from financial document lines. Non-income-coded document amounts stay available in document_amount but contribute zero to this measure.

SUM(Invoices[revenue])

Invoice product cost — Identified product cost summed directly from the selected invoice/credit lines.

SUM(Invoices[product_cost])

Time hours — Recorded hours summed directly from labour detail.

SUM(Labour[hours])

Time cost — Modelled labour cost summed directly from labour detail; this is not actual payroll.

SUM(Labour[labour_cost])

Nonbillable hours — The nonbillable subset of recorded hours. It is already included in total hours and labour cost; do not add it again.

SUM(Labour[nonbillable_hours])

Review and selection

Issue count — Number of review-queue rows. One source can generate several issue types, so this is not a count of unique invoices or services.

COUNTROWS(Issues)

Issue exposure — Sum of absolute amounts associated with warnings. The same source can appear more than once, and different warnings can represent revenue or labour cost. It is neither additive missing cost nor estimated business loss.

SUM(Issues[amount_at_risk])

Selected service — Service name when exactly one distinct name is selected; otherwise the prompt shown below. The drillthrough key is service_id, not the name.

SELECTEDVALUE(Services[service_name], "Select a commercial service")

Cost and scope status — Displays the first matching condition: missing product costs; unreviewed scope; unassigned labour; positive revenue with zero hours; zero revenue with positive hours; otherwise linked before shared costs. It does not summarise every Issues row or independently guarantee completeness.

IF([Missing cost lines] > 0, "Missing product costs", IF([Unreviewed invoice lines] > 0, "Review invoice scope", IF([Unassigned hours] > 0, "Unassigned labour", IF([Recorded hours] = 0 && [Revenue] > 0, "Confirm labour coverage", IF([Revenue] = 0 && [Recorded hours] > 0, "Review invoice timing/link", "Linked; before shared costs")))))

Staff cost reconciliation

Salary budget — Salary-only monthly equivalent for configured employees; excludes contractors and employer costs.

SUM(Staff[salary_estimate])

Salary attributed to time — Actual salary-derived costs of recorded employee time; excludes service-costing assumptions.

SUM(Staff[salary_allocated])

Salary not attributed — Salary budget less attributed salary cost. This is a diagnostic gap, not an additional expense.

SUM(Staff[salary_unallocated])

Scheduled hours — Nominal contracted monthly paid-hours equivalent. Not actual payslip hours.

SUM(Staff[nominal_paid_hours])

Staff recorded hours — All recorded staff time, including contractor and fallback time.

SUM(Staff[recorded_hours])

Staff recorded cost — All recorded staff cost, including salary, contractors and fallback estimates.

SUM(Staff[recorded_cost])

Hours versus schedule — Nominal monthly hours less salary-costed employee hours. Positive means hours not explained by recorded employee work.

SUM(Staff[hours_gap])

Fallback rate hours — Recorded hours costed using the fallback rate.

SUM(Staff[fallback_hours])

Payroll vs salary budget — Whole-business Xero payroll less salary-only budget. Differences may include super, posting timing and coverage.

SUM(Business[payroll_salary_difference])

Maintaining and checking the model

Update the generator alongside any changed TMDL or visual artifact. Full regeneration with python scripts/build_contribution_power_bi.py replaces report files, including Desktop refinements; review the diff and preserve unrelated layouts, filters and connection settings. Keep existing object identities and the calculated Metrics partition. Update this reference when a formula, field source, filter or interpretation changes.

Repository tests check definitions and offline calculations. Verify the updated report and semantic model together in Desktop for loading, rendering and totals; state explicitly when that verification is pending.

tests/test_contribution_report.py covers service attribution, costs, labour, business reconciliation and reporting/write boundaries.
tests/test_commercial_margin_power_bi_project.py checks table/field references, page filters, relationships, canvas bounds and the calculated Metrics partition.
tests/test_reporting_portal.py covers the bounded timesheet collection.
config/commercial_margin.json holds reviewed scope/product/contact/project assignments and expense account-code lists. The deployed file may be selected through COMMERCIAL_MARGIN_CONFIG.

Financial snapshots and audit exports belong outside Git. Use anonymised examples in documentation; refresh-specific amounts remain in the report.