This article was created in collaboration with our expert Łukasz Lelwic, Solution Architect at DataRiseLab.
Summary: In many organizations, the definition of margin is not stored in a single place. Part of the logic is hidden in SQL queries, part in Excel spreadsheets, and part in Power BI measures. As long as the people who originally built the reports are available, the result can usually be explained. The problem arises when teams change, a new subsidiary is onboarded, or another analysis needs to be developed. At DataRiseLab, we treat the margin definition as part of the data architecture rather than knowledge hidden within tools or locked in the minds of individual employees.
Most companies can tell you their current margin. Far fewer can answer an even simpler question:
How exactly was it calculated?
At first glance, the answer seems obvious. After all, the margin is displayed in a report, dashboard, or management presentation. In practice, however, its definition is often scattered across several different places:
-
- a SQL query created several years ago,
- an Excel spreadsheet containing additional adjustments,
- a Power BI measure,
- business rules and agreements that were never formally documented.
For a long time, this approach may work perfectly well. The organization trusts the numbers because the people responsible for reporting know where they come from. The problem emerges when the company grows. A new subsidiary joins the group. Another report is created. The team undergoes organizational changes. At some point, an important question arises:
Which margin definition is the correct one?
And even more often:
Do all reports actually show the same result?
Why can the same margin tell different stories?
In many companies, margin is treated as an objective metric. You simply look at the report and know which product is more profitable. In reality, the answer depends on which costs are included and how they are allocated.
Let’s consider a simplified example. Product A and Product B each generate PLN 100,000 in revenue. Both also incur PLN 60,000 in direct costs.
That is where the similarities end. Both products require 100 hours of production work, but they are manufactured in different ways.
Product A is produced in two large batches. During the month, it requires two machine changeovers, each taking five hours. Product B, on the other hand, is produced in short production runs. To achieve the same sales value, it requires eight machine changeovers of five hours each.
As a result, Product B requires significantly more machine changeovers than Product A.
Let us also assume that the company needs to allocate machine operating costs and setup costs. If overhead costs are distributed proportionally to revenue, both products will receive a similar cost allocation. As a result, the report will show the same margin for each product.
What does a traditional report show?
If the entire pool of overhead costs, amounting to PLN 40,000, is allocated based on revenue, both products will receive the same cost burden:
-
- Product A: PLN 20,000
- Product B: PLN 20,000
As a result, the report will show exactly the same outcome. The margin for each product will be 20%. At first glance, both products appear equally attractive.
What happens when actual resource consumption is taken into account?
Now let us assume that machine operating costs are allocated based on machine hours, while setup costs are allocated based on setup time. Since both products consume 100 production hours, each receives PLN 10,000 in machine operating costs.
The situation is different when it comes to setup costs. The total setup time amounts to 50 hours:
-
- 10 hours for Product A
- 40 hours for Product B
As a result:
-
- Product A is allocated PLN 4,000 in setup costs
- Product B is allocated PLN 16,000 in setup costs
The margin changes significantly:
-
- Product A: 26%
- Product B: 14%
Importantly, the company’s overall financial result remains unchanged. Revenue has not changed. Costs have not changed. The only thing that has changed is the way costs are assigned to individual products.
It is also worth remembering that changing the cost allocation method alone does not generate savings. Reducing the number of setups may initially only free up production capacity. Real value is created only when the company uses the recovered time effectively, reduces overtime, or genuinely avoids additional costs.
Margin after cost allocation should not be the sole basis for deciding whether to accept a new order. Incremental costs and available production capacity are equally important considerations.
Why this example?
Because it clearly demonstrates that margin is not simply the result of a mathematical calculation. It is the outcome of a series of business decisions.
We need to decide:
-
- which costs should be included,
- which business events we want to reflect,
- what allocation key should be used to distribute costs,
- what level of detail is appropriate.
This is precisely why the question “What is the product margin?” should often be preceded by another question: “How exactly was it calculated?”
The most challenging part happens before the margin is calculated
The ERP system knows the products, production orders, costs, and sales. The MES system, or production tracking records, knows the details of machine operations, downtime, and setups. The challenge is that these systems often describe the same events in different ways.
In our example, the setup cost must ultimately be assigned to a specific product. Before that can happen, however, several practical questions need to be answered:
-
- Which production order did the setup relate to?
- Should machine preparation be assigned to the production batch that is about to start?
- How should a single setup event covering multiple production orders be handled?
- What should be done when the end of an event is missing or when time intervals overlap?
If the organization does not agree on how these pieces of information should be linked together, two valid data sources can lead to two different results.
That is why designing a margin calculation methodology should not start with a report. It should start with the data model.
Why does this issue often come to light during a Microsoft Fabric migration?
Many organizations have spent years developing reporting environments based on data warehouses, reports, and processes created by different teams.
It is often only during a migration from a legacy Data Warehouse to Microsoft Fabric that organizations realize how many business definitions are scattered across the company.
Some business logic resides in SQL procedures. Some is embedded in ETL processes. Other parts have been hardcoded directly into reports or Excel spreadsheets.
In practice, a migration is not just about moving data. It is also an opportunity to organize business definitions and establish a single source of truth for the entire organization.
Where should the margin definition reside?
In many organizations, the answer is: “in Excel,” “in SQL,” or “with the controller.” This approach works until a new report is created, a new subsidiary joins the organization, or the team changes. Our approach is based on the belief that knowledge of how business results are calculated should belong to the organization, not to individual tools or people.
That is why we define it across multiple layers of the architecture.
With Microsoft Fabric, this allows us to separate source data from business logic and from the way results are presented in reports. As a result, definitions can be managed, governed, and evolved independently of individual dashboards.
Silver organizes the meaning of data
Before we start calculating margin, we need to ensure that everyone is talking about the same products, customers, and organizational units.
At the Silver layer, we organize data identity and change history. In many projects, we implement this using the Data Vault approach, which allows us to preserve the history of business objects, relationships, and changes over time. As a result, products, customers, and organizational units maintain a consistent identity regardless of changes in source systems. This is where we address issues such as different product identifiers, changes in organizational structures, and inconsistencies across source systems.
Thanks to this approach, the organization can build further analyses on a shared and consistent data model.
Gold defines the business logic
The next step is to determine the granularity of the data, in other words, what a single record actually represents.
Sales, costs, production orders, and operational events often exist at different levels of detail.
This is where the rules are created that help answer questions such as:
-
- How do we allocate transportation costs to sales?
- How do we account for setup costs?
- Which costs should be included in margin calculations?
- How do we handle shared cost pools?
Transportation costs that are settled on a monthly basis also require an explicit rule for assigning them to sales transactions. Without such a rule, different reports may produce different results even when they use the same source data.
This is important because even accurate source data can lead to incorrect outcomes if it is combined in the wrong way. For example, a monthly cost pool that is directly joined to every production event may multiply the allocated values, even though all underlying source data is correct.
For this reason, we separate cost pools from allocation rules within the model and clearly define how costs should be assigned to products, customers, or production orders.
Matching control totals alone does not guarantee a correct business outcome. It is entirely possible to allocate the full cost pool correctly while still assigning some costs to the wrong products, customers, or production orders. That is why allocation rules require not only technical validation but also business validation by finance and operations teams. In practice, every allocation rule should have a designated business owner, a clearly defined scope of application, and a well-defined approach for handling exceptions.
Architecture should be repeatable, while business logic should remain flexible
At DataRiseLab, we use Cairn for Fabric as a set of patterns and components that help organizations build data platforms in a consistent and repeatable way. The framework provides a common approach to organizing data layers, integrating source systems, managing data quality, and supporting analytical processes.
Business-specific knowledge, however, remains within allocation rules, business glossaries, model configurations, and data contracts. This allows organizations to avoid rebuilding the architecture from scratch each time while maintaining full control over their own business definitions.
The semantic model stores the definition of business results
Only at this stage do we define the measures used by business reports:
-
- Margin
- Profitability
- Unit Cost
- Revenue
The Power BI semantic model becomes the place where the business gains access to agreed definitions that are consistently used across all reports.
As a result, creating another dashboard does not mean creating another version of the truth.
What happens when the way margin is calculated changes?
Imagine that a company acquires a new subsidiary. New products are introduced. The finance team decides to change the cost allocation methodology. The definition of profitability changes.
In many organizations, such a change is almost invisible. Someone modifies a SQL query, adjusts a formula in Excel, or updates a report. A few months later, very few people remember when the change was made or why it was introduced. Yet the definition of margin has a direct impact on business decisions.
That is why it should be managed in the same way as application code. A model stored in TMDL (Tabular Model Definition Language) makes it possible to review and version the measures used by Power BI. In practice, version control extends beyond the semantic model itself. SQL queries, cost allocation configurations, and transformation mappings are also subject to change management. Every change should additionally specify whether it applies only to future reporting periods or should also be applied retrospectively to historical data.
Only by combining all these elements is it possible to reconstruct exactly how a business result was calculated at a specific point in time. A change in the way margin is calculated then becomes a transparent and manageable modification, one that can be identified, discussed, tested, and, if necessary, rolled back. This leads to greater transparency and stronger trust in reported business results.
The real value does not come from building another report
Many data projects begin with the question: “What reports do we want to see?” Much less often do organizations ask: “Do we all understand the result in the same way?” Yet this is often where the biggest source of problems lies. If every report uses a different margin definition, the organization will spend its time explaining discrepancies rather than acting on insights.
When the definition is shared, documented, and traceable, conversations can move much more quickly to what truly matters: decision-making.
This is particularly important in Microsoft Fabric environments, where the same data is simultaneously consumed by Power BI reports, analytical processes, and AI-powered solutions.
AI can generate answers incredibly quickly. However, it still relies on clear and unambiguous business definitions to ensure those answers are correct.
The key takeaway
Knowledge of how margin is calculated should belong to the organization. Not to the report author. Not to the person who wrote the SQL query. Not to a single expert who happens to remember how the metric was originally defined.
A business metric definition should have an owner, a defined place within the data architecture, and full traceability of how it is calculated. This ensures that every new report uses the same knowledge base, new employees do not have to reconstruct historical decisions, and new analyses, dashboards, or newly acquired companies can all rely on the same agreed-upon definition. As a result, the organization can move more quickly from explaining numbers to making decisions.
When knowledge about how a business metric is calculated becomes part of the data architecture, every new report, analysis, or newly integrated company benefits from the same definition. This allows discussions about profitability to move faster from explanations to decisions.