Commercial contribution model reference

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

1. 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.
2. 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.
3. /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.
4. 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.
5. 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.

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 44 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 44 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])

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")))))

Maintaining and checking the model

Edit the generator and backend sources, then regenerate Power BI definitions with python scripts/build_contribution_power_bi.py. Do not rely on a manual edit to a generated TMDL or visual file surviving a rebuild. Update this reference when a formula, field source, filter or interpretation changes.

• 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.

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])