Executive Financial Dashboard

The starting point was a full brief of the kind a CFO would write: a supplier of precision components, around 180 employees, group revenue in the range of CHF 25 to 30 million, four markets. Along with it came four mandatory requirements, three optional ones, strict design constraints and one sentence that set the tone: the board looks at the screen for ten seconds and has to know afterwards whether the company is on track.

Three pain points

• Margin erosion in the euro markets. Revenue grows, operating profit falls. The suspicion was that specific product and region combinations were structurally loss-making — but it couldn't be proven.
• Budget discipline. The question isn't whether there is a variance, but where and since when.
• Operating cost drift. Since an ERP project, operating costs have been growing faster than revenue. That needed to be visible, not buried in a footnote.

Data model

The fact table holds revenue, cost of goods sold, operating expenses and a revenue target per quarter, product and region. Product master data adds a target margin for each product.

• Star schema with one fact table and dimensions for product and date
• A dedicated calendar table, marked as a date table — the precondition for any time intelligence
• Filters flow from the dimensions to the fact table only, no bidirectional relationships
• Row-level security designed in from the start: regional managers see their region, the board sees everything

The trap of the incomplete year

The data ends on 31 July 2026. A naive year-on-year comparison puts seven months against twelve and produces a revenue collapse that doesn't exist. The brief flagged this explicitly as a quality criterion — rightly so, because a number like that in a board meeting costs trust you don't get back.

Solved with time intelligence on the calendar table rather than with year numbers:

Revenue PY =
CALCULATE (
    [Revenue],
    SAMEPERIODLASTYEAR ( Dim_Calendar[Date] )
)

The report now always compares the same slice of the year — January to July against January to July.

Weighting target margins correctly

Every product carries its own target margin, from 18 per cent on turned parts to 40 per cent on service contracts. The average of those six percentages is worthless as a group target: it treats a product with CHF 0.6 million in revenue the same as one with CHF 2.5 million, and produces a priority list pointing the wrong way.

The correct approach weights row by row against actual volume:

Target margin weighted =
DIVIDE (
    SUMX (
        Fact_Financials,
        Fact_Financials[Revenue_CHF]
            * RELATED ( Dim_Products[Margin_Target_%] ) / 100
    ),
    [Revenue]
)

Only then can you answer the question a CFO actually cares about: not who is furthest behind in percentage terms, but where the largest amount of profit is being left on the table.

Three pages, three audiences

• Executive board. A KPI row with prior-year and target comparison and restrained traffic-light logic. Below it the divergence chart: revenue as columns, net margin as a line on top — exactly the gap that triggered the project. Next to it the margin bridge, splitting the year-on-year difference into revenue, COGS and operating cost effects.
• Portfolio and budget. Six products against four regions as a conditionally formatted matrix, so loss-making cells stand out immediately. Alongside it, actual against target per region, in absolute and percentage terms.
• Scenario simulator. Three what-if parameters for price, material cost and operating cost. In the meeting itself you can play through what a three per cent price increase in the DACH region, or a five per cent material cost shock, does to the result.

What cost me the most time

The margin bridge. A waterfall chart needs artificial categories — opening value, revenue effect, COGS effect, operating cost effect, closing value — that must appear in a fixed order. Those categories come from a disconnected helper table, and the values arrive through SWITCH logic. Sort that helper table by a column that itself refers back to the measure and you create a circular dependency, at which point Power BI refuses to build the model. The answer is a separate, constant sort column — obvious once you understand it, tedious until then.

How I worked

I built the report with Gemini as my assistant. The loop stayed the same throughout: describe the requirement, receive a suggestion with a concrete DAX formula, implement it in the model — then take the step apart. Why is this function here, what happens without it, and does the result hold under filters the suggestion never considered?

That's my way of learning, not a shortcut. A working result I can reason through backwards gets me further than starting from zero and spending the first week on syntax errors. The three points above — the incomplete year, the weighted target margins, the circular dependency — are exactly the places where the suggestion didn't hold and I had to correct it. That's where I learned the most.

What I'm not claiming is that this report came out of my head freehand. What I am claiming is that I can explain every line in it: why it looks the way it does, what happens if you change it, and where it breaks.

Status

The data model, the measures and all three pages are finished and embedded interactively above. The embedded version carries no row-level security — Microsoft's public publishing feature doesn't support it. The role concept from the brief is implemented in the model and can be shown on request. What's still running is the last of the visual work against the design constraints: restrained, printable, suitable for an annual report.