Cutting a two-year cost variance from 48% to under 2%

When I joined the team at Alshaya, the main sales dashboard was reporting the wrong margins for around 10 Oracle RMS brands. The sales were fine. The problem was the cost behind the margin, which was inaccurate, and it threw the reported margins off from finance by nearly 48%. The gap had stood for almost two years. I traced it through the data platform, found two root causes, and worked with engineering to fix both. The variance dropped to under 2%.

The problem

The margins on the main sales dashboard did not match the official finance figures, off by close to 48% across about 10 brands. The sales numbers were correct. The cost feeding the margin was not, and wrong cost means wrong margin. When reporting is off by that much, people stop trusting it. Finance and category teams could not rely on the margin numbers, and decisions that should have leaned on the dashboards were held back.

The issue had existed for nearly two years. The team had tried to solve it more than once, but limited capacity meant it was never fully chased down.

How I approached it

Our data platform runs on Azure using a Medallion architecture, Bronze then Silver then Gold, with the Gold data mart feeding Power BI. If a number is wrong at the end, it entered somewhere along that path. So my plan was simple: follow the data through each layer and find exactly where the gap appeared.

I started by categorizing the brands, and a pattern showed up quickly in the Home Improvement brands. From there I drilled down step by step, from brand to period, to day, and finally to individual products, comparing the Power BI data against the finance data side by side. Narrowing from the category down to a single row is what exposed the cause.

Root cause one: pack items

At product level, several items were missing cost entirely. When I looked closer, they were all pack items, collections sold as one unit. A table and four chairs, for example, sold as a single pack, but the cost only existed for the individual components.

The mismatch was in the keys. The sales table held the pack item number, while the cost table held only the component item numbers. The existing logic looked up the pack number in the cost table, found nothing, and returned no cost. That missing cost was a large part of the 48%.

I validated the pattern across many examples, then worked with the data engineering team to add a new business rule. The logic now checks whether an item is a pack or a standard item first. If it is a pack, it references the component items, then pulls their cost from the cost table. That closed most of the gap across the Home Improvement brands.

Root cause two: concession brands

The second cause sat with concession brands. These products are supplier-owned, so a standard product cost simply does not exist for them. The old logic had nothing to find.

Working with the Data Architect, I traced the correct source and designed a new data model built on the agreed supplier margin percentages. I helped define the business logic, validated the SQL, and made sure the data flowed correctly through the pipeline and into the Power BI dashboards.

The result

Together, the two fixes brought the Power BI reporting back in line with finance. The variance fell from nearly 48% to under 2%. More than the number, it restored trust: finance and category teams could rely on the margin dashboards again, and the reporting could be used for decisions instead of being questioned.

What I would tell another analyst

When a total is wrong, do not argue with the number. Trace it. Narrow from the category down to the single row, and the cause usually shows itself.

The variance almost always hides in the edge cases the general logic never planned for, here it was pack items and supplier-owned goods, not the ordinary products. And the fix is often a business rule, not a clever formula. Get engineering and finance into the room early, because the answer usually lives across all three.