The Challenge
Connecting Purchases Back to Customers
Business Central links sales to customers and purchases to vendors, limiting direct visibility into customer-wise purchase costs and margins.
Missing Customer Link
Purchase records are linked to vendors, with no native direct relationship showing the customer associated with each purchase.
Separate Transactions
Open and invoiced transactions reside across different Business Central tables, complicating customer-level analysis.
Purchase Attribution
Purchase amounts could not be directly attributed to customers without linking corresponding sales and purchase records.
Margin Visibility
Without customer-wise purchase attribution, analyzing sales, purchase costs, and gross margin by customer was difficult.
Objective
What We Set Out to Achieve
Enable customer-level purchase and margin analysis by connecting Business Central sales and purchase transactions in Power BI. The solution needed to map open and invoiced purchases to their originating customers using shared Order Code and Project Code values while supporting analysis of invoiced purchases, open purchases, sales, and gross margin.
Our Approach & Solution
How We Connected Purchases to Customers
Power BI uses a composite OrderProject Key, customer lookup tables, DAX logic, and model relationships to connect Business Central purchases with customers.
01
Build Composite Key
Order Code and Project Code were combined into an OrderProject Key across relevant Business Central tables, creating a common identifier for matching sales and purchases.
02
Map Customer Records
Customer lookup tables for open and invoiced sales map each OrderProject Key to its customer, providing the bridge needed for purchase attribution.
03
Enable Customer Analysis
DAX LOOKUPVALUE logic assigns customers to purchase records, while model relationships enable customer-wise sales, purchase, and margin analysis.
Results & Impact
Customer-Level Financial Visibility
The solution connects purchase costs with customers, enabling analysis of open purchases, invoiced purchases, sales, and margin.
360°
Customer Cost Visibility
Conclusion
The Bigger Picture
By combining Order Code and Project Code into a shared OrderProject Key, the Power BI solution bridges the gap between customer-linked sales and vendor-linked purchase transactions in Business Central. Customer lookup tables and DAX logic enable both open and invoiced purchases to be associated with the relevant customer. This provides clearer visibility into customer-wise purchase costs, sales, and gross margin while supporting more informed financial and operational analysis.