Power BI variance analysis

The first variance measure most people write in Power BI is this one:

Variance = [Actual] - [Budget]

It is right for revenue and backwards for every cost line underneath it. Spend $58,000 more on marketing than you planned and that measure returns a positive number, which your conditional formatting then paints green.

Fixing that is three lines of DAX and it is the thing this page is really about. The rest is the model you need underneath it and the one question a variance chart can never answer for you.

What the model has to look like first

Most broken variance reports are broken in the data model, not in the visual. The symptom is always the same: actuals and budget live in two separate tables, so every comparison needs a join that falls over the moment a store or an account exists in one table and not the other.

Stack them instead. One fact table, one row per account per location per month, and a column that says which version the row is. Actual and budget become filter contexts rather than columns, and the model stops caring how many versions you add later.

Column	Example	Why it is there
Account	Marketing	The reporting line, from your account dimension
Version	Actuals or Budget	The whole trick. Filter on this, do not join on it
Location	Hell’s Kitchen	Whatever you slice by
Month	Mar 2025	Joined to a real date table, not a text month
Value	92000	One number column, always positive as entered

Then a date table marked as a date table, an account dimension, and a location dimension. If you are building the model from a messy export, do the shaping in Power Query before the data ever reaches the model.

The four measures that do the work

Written as measures, not calculated columns. A calculated column computes once at refresh against the whole table and cannot respond to a slicer, which is why the column version of this always looks right in the table and wrong in the chart.

Actual     = CALCULATE( SUM( Financials[Value] ), Financials[Version] = "Actuals" )

Budget     = CALCULATE( SUM( Financials[Value] ), Financials[Version] = "Budget" )

Variance   = [Actual] - [Budget]

Variance % = DIVIDE( [Variance], [Budget] )

Use DIVIDE rather than the slash operator. A store that opened mid-year has months with no budget, and DIVIDE returns blank on a zero denominator where the slash returns an error that propagates across the whole visual.

The sign problem, and the fix

Now the part that gets skipped. A favorable variance means more revenue or less cost, so the arithmetic runs in opposite directions on the two halves of the P&L. One measure cannot serve both unless it knows which half it is looking at.

Put the answer in the account dimension, where it belongs, as a sign column: 1 for revenue accounts, -1 for expense accounts. Then:

Variance (F/U) =
SUMX(
    VALUES( Accounts[Account] ),
    ( [Actual] - [Budget] ) * SELECTEDVALUE( Accounts[Sign] )
)

The SUMX matters. Iterate over the accounts rather than reading a single sign, or the measure returns blank the moment somebody looks at a subtotal that spans revenue and cost lines, which is every P&L total you will ever put on a page.

Format on this measure and the colors finally mean what people assume they mean. Red is bad, green is good, on every row.

Build the visual around the question, not the data

A matrix with accounts down the side and actual, budget, variance and variance percent across the top answers the reporting question. A waterfall answers a different one, which is where the total went, and it only works if you feed it the favorable and unfavorable measure rather than the raw one.

Three settings do most of the work on the matrix. Conditional formatting driven by the F/U measure, not by the raw variance. A percentage threshold on the background rule so tiny variances stay uncolored and the eye goes where it should. And drill-through from the total to the store detail, so somebody reading the summary can get to the cause without asking you for it.

Keep card visuals for the two or three numbers a reader has to leave with. Everything else belongs in the matrix. If you want the fuller argument on layout, dashboard design covers it properly.

A month that reads three different ways

Here is why the measures matter, on numbers you can check rather than a story about a company I made up. F9 Coffee Co. is the seven-store chain I use for teaching. Take one store, Hell’s Kitchen, in one month, March 2025.

Comparison	Reads as	Number
Revenue versus February	Growth	$269,368 to $300,899, up 11.7%
Revenue versus budget	A miss	$300,899 against $340,000, down 11.5%
Marketing versus budget	An overspend	$92,000 against $34,000, up 170.6%

Every one of those is true. Put the first on a slide and the month is a success story. Put the third next to it and it is a campaign that cost $58,000 more than planned and left the store 11.5% short of the number it was supposed to hit.

That is what a variance page is for. Not to produce a number, which any tool will do, but to stop a reader landing on the flattering comparison by accident.

The same thing happens at chain level, in the other direction. Across 18 months revenue came in 1.0% under budget, which looks like a business running to plan. Add up the 42 store-by-account variance cells without letting them cancel and the gross movement is $3,671,793. Almost $3.3 million of it nets off inside that 1%.

What the chart cannot tell you

Back to March. The measure tells you marketing ran 170.6% over. It does not tell you why, and this is the honest limit of every variance report ever built.

The reason is in the store manager’s written commentary for that month: an influencer and local advertising campaign that significantly overspent and produced very little incremental traffic. Nothing in the P&L contains that. Nothing in Power BI could derive it. It exists because somebody who was there wrote it down.

Which is worth remembering when you point a language model at a variance table and ask it to explain the movement. It will describe the movement accurately and then invent the cause, fluently, because the cause is not in the data you gave it. Let it draft the description and leave the explanation to the people who were in the room.

Getting started

Build it in this order and you will not have to unpick it:

1. Stack actual and budget into one fact table with a Version column.
2. Add a real date table and mark it as one.
3. Add the sign column to your account dimension while you are in there.
4. Write the four base measures, then the F/U measure on top of them.
5. Build the matrix, format on the F/U measure, and set a materiality threshold.
6. Only then start on the waterfall and the drill-through.

Steps one and three take about ten minutes and save every hour after them. Most of the variance reports I have been asked to fix skipped both, and none of the fixes were in the visual.