Invoice Profit (Line-Level, Vendor-Attributed)

A Power BI model that calculates invoice profitability with drilldown to item lines, reliable vendor attribution, and clean COGS + freight/charges handling from Business Central.

Power BIBusiness CentralProfitabilityModelingDAX

Summary

Problem

Profit reporting was hard to trust because vendor attribution was inconsistent from invoice web services, invoices can contain items from multiple vendors, and freight/charges + item COGS can easily duplicate or misalign at the line level.

Solution

Built a line-based profit fact table with defensible vendor mapping (Item → Item Card → Primary Vendor), item-level COGS from Value Entries, and invoice-level freight/charges logic that avoids duplication.

Result

A drillable invoice → item profitability view with reliable Vendor and Sales Person slicers, clean rollups, and a KPI dashboard that supports last-month performance review.

Business Impact

Vendor profit is defensible

Vendor mapping is reliable even when invoices contain multiple vendors because vendor is assigned per line using Item No → Item Card → Primary Vendor.

COGS is correct at item line level

COGS is sourced from Value Entries (Cost Amount Actual) and aggregated by InvoiceNo + Item No, with allocation handling for duplicate lines of the same item on the same invoice.

Charges avoid duplication

Freight/charges are treated as Account lines and summarized at the invoice level. Charge amount uses Unit Price with a fallback to Line Amount to handle invoices where Line Amount = 0.

Gallery

Screenshots and artifacts from the build (sanitized where needed).

1) Vendor attribution approach

Invoice lines do not reliably return Buy-from Vendor via web services, so vendor attribution was standardized to: Vendor = current (primary) vendor on the Item Card. Mapping occurs at the invoice line level using Item No → Item Card → Vendor_No to support filtering and rollups by Vendor No/Vendor Name.

2) Line-based fact table for drilldown

The profit model was shifted to a line-based fact table so invoices can be summarized at the invoice level while still supporting drilldown into item-level detail (critical when invoices include items from multiple vendors).

3) COGS from Value Entries (item/document grain)

COGS comes from Value Entries (Cost_Amount_Actual). Because cost posts at the item + document reference grain, COGS was aggregated by InvoiceNo + Item No and allocated across duplicate invoice lines for the same item on the same invoice when applicable.

4) Charges/Freight logic (no duplication)

Freight/charges are Account lines, so charges are summarized at the invoice level (not forced onto item lines). Some invoices contain freight lines where LineAmount = 0 but UnitPrice holds the real charge, so the rule was updated to: Charge Amount = UnitPrice (fallback to LineAmount) and Qty is ignored due to inconsistent behavior.

5) Time filtering + UX

Relationship: DimDate[Date] → Fact_InvoiceProfitLines[PostingDate] (one-to-many, single direction, active).
Added slicers for Vendor and Sales Person.
Converted the profit view to a Matrix so users can drill invoice → item.

Current output

Invoice Count
Total Revenue
Total Charges
Total Net Revenue
Total COGS
Total Profit
Total Losses (sum of negative profit)
Profit Margin %

DAX Library

Search and review the KPI logic used in this model (sanitized).

Sort

Showing 9 of 11 measures