How to Build a Power BI Budget vs Actual Report

The pattern is the same whether you are tracking a departmental cost center or a company-wide P&L: two streams of numbers — what you planned and what actually happened — compared across the same time periods and account structure. The hard part is almost never the subtraction. It is getting the model right so that every comparison is apples-to-apples, the totals tie out, and the report stays fast and trustworthy as the year fills in. The sections below follow the order we work in on real engagements.

How should you model the data for budget vs actual?

Keep Actuals and Budget as two separate fact tables, each joined to shared Date and Account dimensions. Resist the urge to mash them into one table with a “scenario” column on day one — separate facts keep refresh logic clean and let the two streams arrive at different grains.

The shape that works best is a small star schema (opens in new tab). You have two fact tables — Actuals and Budget — and two or three dimension tables they both connect to: a proper Date table, an Account (or chart-of-accounts) table, and usually a Department or cost-center table. Both facts relate to the dimensions on a one-to-many basis, with the dimension on the “one” side. Because both facts share the same dimensions, a single slicer on department or month filters Actuals and Budget identically, which is exactly what makes a clean variance possible.

The Account dimension deserves special care. Budgets are usually set at a rolled-up level — “Marketing,” “Travel,” “Salaries” — while actuals post against granular GL accounts. Map both to a shared account hierarchy so that a budget line and the actual postings beneath it meet at a common node. If your source data does not already share account keys, build a mapping table; that mapping is the quiet foundation the whole report rests on.

What DAX measures does a variance report need?

You need four core measures: Actual, Budget, Variance (Actual minus Budget), and Variance %. Build them as explicit measures, not implicit drag-and-drop sums, so every visual and every drill-down reuses the same definitions and your numbers stay consistent everywhere.

Start with the two base measures that simply sum each fact:

Actual = SUM ( Actuals[Amount] )

Budget = SUM ( Budget[Amount] )

Then the variance, which is just the difference:

Variance = [Actual] - [Budget]

For the percentage, always use DIVIDE so a zero or blank budget returns a clean blank instead of an error that breaks the visual:

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

Taken together, those four measures are the entire backbone of the report. The table below lays them out side by side — the measure name, its DAX, and what each one tells a reader — so you can drop them into a fresh model and have a working variance pack in minutes.

One subtlety trips people up: for revenue, a positive variance is good, but for expenses a positive variance (spending more than planned) is bad. Rather than baking sign logic into one measure, it is cleaner to keep Variance arithmetic and let conditional formatting interpret favorable versus unfavorable per account type. If you do want an “is this favorable?” flag, a SWITCH driven by an account-type column on the Account dimension keeps the rule in one place.

When you start filtering these measures — year-to-date budget, full-year plan, prior-period actuals — CALCULATE becomes the workhorse, because it evaluates a measure while changing the filter context. Microsoft's CALCULATE function reference (opens in new tab) is the page to keep open while you write these. A common need is a year-to-date variance, which you assemble from year-to-date versions of the base measures:

The TOTALYTD function (opens in new tab) rolls each base measure up to the year:

Actual YTD = TOTALYTD ( [Actual], 'Date'[Date] )

Budget YTD = TOTALYTD ( [Budget], 'Date'[Date] )

Variance YTD = [Actual YTD] - [Budget YTD]

How do you handle a monthly budget against daily actuals?

This is the classic granularity mismatch: budgets are set per month, but actuals post daily. The two facts live at different grains, so you cannot relate them directly. The fix is to connect both to the Date dimension at the grain each one actually has, and let the date table reconcile them.

The cleanest approach is to give the Budget table a date key that points at the first day of each month, then relate it to your Date table on that key. Actuals relate to the same Date table on their daily date. Now when you view the report by month, both facts aggregate up to the month and compare correctly. The danger only appears when a user drills to a single day: there is no daily budget, so the budget figure should sensibly show as the monthly total or blank, not a misleading fraction.

If business users genuinely need a daily budget line — for pacing or burn-rate views — allocate the monthly budget across days rather than pretending it was always daily. A simple even spread divides the month's budget by its day count:

Budget Daily = DIVIDE ( [Budget], COUNTROWS ( VALUES ( 'Date'[Date] ) ) )

That is deliberately naive — it spreads evenly and ignores weekends or seasonality. When pacing matters, weight the allocation by working days or by a seasonality profile stored on the date table. The principle holds either way: keep each fact at its native grain, and let the shared date dimension do the reconciling rather than forcing the data into a grain it never had.

How should you visualize budget vs actual?

Lead with variance, not raw numbers. Executives do not want to eyeball two columns and do mental subtraction — they want to see, at a glance, where reality diverged from plan and by how much. The strongest layouts pair KPI cards at the top with a variance-focused chart and a detail table below.

A few proven building blocks:

• KPI cards for the headline numbers — total Actual, total Budget, and Variance % — so the bottom line reads in two seconds.
• Variance bars rather than side-by-side actual/budget columns. A single bar per account showing the variance, colored by direction, communicates far faster than two bars the eye has to compare.
• Conditional formatting on the detail table — background or font color on the variance column, driven by your favorable/unfavorable logic — turns a wall of numbers into a scannable heat map.
• A clean account hierarchy in the table so an executive can expand from a department total down to the line items driving an overage, all in the same visual.

Use color with restraint and consistency: pick one hue for unfavorable and one for favorable, and use them only to signal variance direction so the meaning never has to be re-learned. Avoid encoding the same information three different ways on one page; a tight, opinionated layout reads as more credible to a leadership audience than a crowded one.

How do you design the report for executives?

Design for a thirty-second read. An executive should land on the page, grasp whether the organization is on, over, or under plan, and know which two or three lines need attention — before scrolling or clicking anything. Everything else is supporting detail.

That means a deliberate visual hierarchy: the most important number is the largest and highest on the page, secondary context sits below it, and granular detail comes last for those who want to dig. Write labels in business language, not model language — “Operating Expense Variance,” not [Variance]. Round to the precision a decision actually needs; nobody approves a budget to the penny, so show thousands or millions and let the drill-through carry the detail.

Add light guidance the data alone cannot give. A short commentary box explaining the two biggest drivers of the month's variance turns a report into a briefing, and it is the single feature finance leaders ask for most. Keep filters minimal and obvious — period, department, scenario — and set sensible defaults so the page opens on the current month already in focus.

What are the most common budget vs actual mistakes?

Most broken variance reports fail in the model, not the visuals. The errors below are the ones we are called in to untangle most often, and nearly all of them trace back to how the two facts and the dimensions were wired together.

• Joining Budget directly to Actuals. The two facts should never relate to each other — only to shared dimensions. A direct join creates duplicated rows and inflated totals that look plausible until someone checks them.
• Ignoring the granularity mismatch. Relating a monthly budget to a daily date without thinking through drill-down produces nonsense at the day level and erodes trust in the whole report.
• Dividing without DIVIDE. A plain division on a zero or blank budget throws an error that can blank an entire visual; DIVIDE returns a tidy blank instead.
• Mismatched account mapping. If budget lines and GL postings do not roll up to common account nodes, variances appear against the wrong rows and the totals quietly drift apart.
• Treating every variance sign the same. Without account-type awareness, an expense overage and a revenue beat get colored identically, which misleads the exact audience the report is for.

If you want to go deeper on the formula language behind these measures, our explainer on DAX measures versus calculated columns covers why these comparisons belong in measures, and the engineering behind a sound star schema is the focus of our data modeling and DAX work.

Putting it together

A reliable budget vs actual report is mostly a modeling exercise dressed up as a reporting one. Separate Actuals and Budget facts, both hanging off shared Date and Account dimensions; four clean measures for Actual, Budget, Variance, and Variance %; a deliberate plan for the monthly-versus-daily mismatch; and a layout that puts variance front and center for a busy reader. Get those four things right and the report will tie out, stay fast, and survive the scrutiny of a board meeting. This kind of finance reporting is the bread and butter of our Power BI work for finance teams — if your variance pack does not tie out today, it is exactly the sort of fix a few prepaid hours resolve.