← Portfolio / Selected work

Flagship · Microsoft Fabric · Completed

The chain was growing.
And still losing money.

A pharmacy chain with 1,000 branches sold more every year, yet it kept losing money. I traced the data from the cash register to the dashboard and found where the money goes: 314 products that cost more to buy than they sell for.

Landing page of the Power BI report: revenue 3.13 billion SAR, 314 margin-destroying products, 114 million SAR loss below cost, 33.4% trusted margin
Report landing page · synthetic data, Saudi chain, 2020–2025.
114MSAR lost on 314 products (2020–2025)
99.8%of the loss below cost comes from them
33%margin on every other product
0rows lost from source to report

Like a doctor:
rule out, then find the cause.

The analyst examines each part of the business, removes the healthy ones, and points at the disease. The manager decides what to do.

01The patient looks healthyRevenue grows about 10% a year.
Executive page: revenue 654.5 million SAR in 2025, up 10% on last year, with a map of Saudi Arabia

What you see

Sales rise every year, in every region and branch type. Nothing on this page looks wrong, which is exactly why the problem was hard to find.

02The branches are not the causeWeak branches are spread evenly, not one region.
Branches page: 104 branches in the bottom 10% of their peer group, scatter of sales per hour against branch score

What you see

Each branch is compared only with similar branches (same size and city type). The weakest 10% appear in red, as a list to review, not to punish. Click a branch to open its own profile.

03The hidden disease314 products are bought at about 10x their price.
Products page: 314 products cause 23.97 million SAR loss in 2025; table with the supplier discount needed for each product

What you see

These products are 2.6% of sales but cause almost all of the loss. For each one, the report shows the discount the supplier would need to give for a 20% margin: often above 90%. The decision becomes simple: renegotiate, check the pack size, or stop buying.

04What we checked, honestly7 fraud signals tested; none proved. 29 data limits listed.
What we checked page: integrity signals and data fingerprints

What you see

Before blaming any person, the analysis tested seven common fraud patterns. None was stronger than chance, so nobody is named. The real gap is a missing control: nothing stops a product being bought above its selling price.

In one sentence

Sales were growing, branches were working, and the company was still bleeding. The disease was not where everyone was looking.

From diagnosis
to treatment.

Five actions in order of urgency. The analyst recommends; the manager decides.

  1. 01

    This week: stop the bleeding

    Freeze new purchase orders for the 314 products. First check the pack size with procurement: a cost about 10x the price often means a box was recorded against a single strip.

  2. 02

    Within 30 days: renegotiate or delist

    For each product the report shows the supplier discount needed for a 20% margin. If the supplier cannot reach it, delist the product, unless it is an essential chronic medicine with no alternative; then keep it and offer a therapeutic substitute.

  3. 03

    Within 60 days: close the gap that allowed it

    Block any purchase order whose cost is above the selling price without a manager's approval. Ask for three quotes, separate who orders from who approves, and review a monthly exception report.

  4. 04

    Every month: review branches, fairly

    Visit the red branches with their peer comparison in hand. Start with expenses, the main driver of low scores, not sales. Never score pharmacists on customer returns.

  5. 05

    Next quarter: record what is missing

    Log expiry and damage write-offs with a reason, give each product one owner in procurement, and store monthly branch targets. Then the next diagnosis can measure what this one could not.

Expected effect

If the 314 products stop selling at a loss, the 2025 margin rises from 29.3% to about 33%, the level every other product already earns. That is up to 24M SAR a year (estimate on synthetic data).

Decisions a pharmacist
would make differently.

Ten years in retail pharmacy shaped what the report measures, and what it refuses to measure.

01

Returns are the customer's right

Scoring pharmacists on returns would push them to refuse returns. So returns were removed from the branch score.

02

No invented numbers

Expiry and damage matter most, but the data has no write-off records. The report says so instead of faking an indicator.

03

No blame without evidence

Buyers of the loss-making products behave like all other buyers. The finding is a process gap, not a person.

04

I found my own bug

An average that was exactly 0.40 instead of 0.50 led to a real error in the data layer. It was fixed before publishing.

How it was built.

End-to-end on Microsoft Fabric, with every layer reconciled to the riyal and stored in Git.

01 / Bronze

Lakehouse · PySpark

72 source tables, 60,234,021 rows, reconciled to the raw files with a difference of 0.

02 / Silver

Cleaned · PySpark

58 tables with keys, types, money as decimal(18,2) and business-rule checks.

03 / Gold

Warehouse · T-SQL

Star schema: 9 dimensions, 9 facts, 4 analytical tables. 23 stored procedures rebuild everything in about 2 minutes.

04 / Report

Direct Lake · Power BI

Measures written in TMDL, 6 pages, dynamic titles, drill-through, performance under 2 s per visual.

+Branch profile (drill-through)One branch against its peers, month by month.
Branch profile page: one branch score against the peer average and revenue against last year

Right-click any red branch, choose Drill through, and see when its score dropped and how its sales moved.

Full code, decisions and reconciliation on GitHub ↗

Before you ask.

Is this real company data?

No. It is a synthetic dataset of a fictional Saudi pharmacy chain. The report lists 29 patterns where the data does not behave like a real chain, instead of hiding them.

Why is there no live dashboard link?

The report runs on a Microsoft Fabric capacity. The screenshots above are exported directly from it; the code to rebuild everything is on GitHub.

Pharmacist · Junior Data Engineer & Data Analyst

Let’s find where
your data hides the money.

Open to Junior Data Engineer, Data Analyst and BI roles in healthcare and retail.