My first case-study dashboard: three things I underestimated

The first case study is live: an executive financial dashboard for a supplier of precision components. Four mandatory requirements, three optional ones, strict design constraints, and the 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.

The case study describes the result. This post is about how I got there — and the three places where it didn't run smoothly.

How I worked

I built the report with Gemini as my assistant. The loop was always the same: I describe what the next requirement asks for, get back a suggestion with a concrete DAX formula, implement it in the model — and then take it apart. Why is this function here? What happens if I drop it? Does the result still hold once I apply a filter the suggestion never considered?

That's a deliberate choice. I learn faster with a working result in front of me that I can reason through backwards. Start from zero and you spend the first week on syntax errors instead of on the data model — and syntax isn't what makes a dashboard right or wrong.

It gets interesting where the suggestion doesn't hold. That happened three times, and those three taught me more than everything else combined.

1. The incomplete year

The data ended in late July. My first year-on-year comparison put seven months against twelve and showed a revenue drop of over forty per cent. The number was wrong but looked entirely plausible — and that is the dangerous part. A report that is obviously broken gets fixed. One that credibly claims something false ends up in a meeting.

The fix isn't a formula, it's a modelling decision: a dedicated calendar table, marked as a date table, with time intelligence running on it rather than comparisons across year numbers.

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

The report now compares January to July against January to July, with no year hard-wired anywhere.

2. Percentage targets don't average

Every product carried its own target margin, from 18 to 40 per cent. My first attempt took the average of those six values as the group target. That's convenient and it's wrong: a product with CHF 0.6 million in revenue then counts for exactly as much as one with CHF 2.5 million.

The consequence isn't a rounding error, it's a priority list pointing the wrong way. Five points behind on a small product looks more dramatic than two points on the revenue driver — in francs it's the other way round. 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]
)

Since then, the first thing I check about any percentage metric is whether it can be aggregated across a level at all. Margin, rate, share — for all three the answer is almost always: not as an average.

3. The margin bridge and the circular dependency

A waterfall chart needs categories that don't exist in the data: opening value, revenue effect, COGS effect, operating cost effect, closing value. They come from a disconnected helper table, with the values arriving through SWITCH logic.

My mistake was the sorting. I wanted to order the categories by their value — and that value comes from the measure, which in turn reads the category. Power BI reported a circular dependency and refused to build the model. The answer is a simple, constant sort column in the helper table. Two minutes of work, after half a day spent looking in the wrong place.

What that taught me about working this way

The three points above have one thing in common: no tool flagged any of them. The wrong year-on-year comparison ran through without an error — I caught it because the number didn't match what the brief said about the company. That a percentage average is useless as a group target appears in no documentation; it only emerges once you ask what the number is for. And the circular dependency was a question of understanding the data model, not of syntax.

An assistant gives you a formula that fits your question. Whether it was the right question is decided by nobody but you. That's where the work sits — and that's exactly why the method works: I see a result quickly, and reasoning through it forces me to hold every step against the requirement instead of just accepting it.

The brief itself also came from an AI — I generated it with Claude. In hindsight that was the most valuable part: an assignment you write yourself only contains requirements you can already meet. One written by somebody else demands a margin bridge, a scenario simulator and a role concept, and so asks questions you can't answer yet.

My standard: I don't claim to have built this freehand. But I can explain every line of DAX in this project — why it looks the way it does, what happens if you change it, and where it breaks. Anything I can't explain doesn't go into the portfolio. How I work with AI is set out in full on the transparency page.

What comes next

The visual work is still running; after that the report will be embedded in the case study interactively. Three more projects are under way. If you have a model behaving strangely, write to me — the best posts come out of real cases.