Power BI Actual vs Forecast: 3 Ways to Compare

Every FP&A team eventually asks the same question: how close did we get? Sales forecast a number in January, actuals land in February, and someone — finance, ops, sometimes the CEO — wants to see the gap. Not just the two numbers side by side, but the difference, the direction, and whether it’s a pattern or a one-off miss.

Power BI doesn’t have a single built-in “Actual vs Forecast” visual. Below are the three ways teams actually build it.

1Way 1: DAX Measures in a Matrix Table

This is the standard approach, and the one most Power BI tutorials teach first. It assumes your fact table has a Scenario column (Actual / Forecast, or Actual / Budget / Forecast) rather than actuals and forecast sitting in separate tables.

The core measures:

Actual = CALCULATE(SUM(Sales[Amount]), Sales[Scenario] = "Actual")

Forecast = CALCULATE(SUM(Sales[Amount]), Sales[Scenario] = "Forecast")

Variance = [Actual] - [Forecast]

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

DIVIDE matters here — a plain / throws an error the moment a forecast value is zero, which happens more often than you’d expect in a new product line or a paused region.

Drop Actual, Forecast, Variance, and Variance % into a matrix alongside Region, Product, or Month. Power BI’s native conditional formatting applies a background color scale and an icon set directly to the variance cells — no separate visual needed.

Where this breaks down:

For a single, static report, this is fine. For a report that needs to flex across grain, scenario type, and multiple metrics, the measure count becomes the actual maintenance burden.

2Way 2: Variance and Bullet Charts

The second approach keeps the same DAX foundation from Way 1 but changes how the result is displayed. The realistic, native version of this is Power BI’s Line and clustered column chart: Actual as columns, Forecast as a line overlay, using the report’s default theme colors.

Certified AppSource visuals extend this further with true bullet charts and dedicated variance charts, but the native combo chart above is what most teams reach for first, and it still needs the same Actual, Forecast, and Variance measures from Way 1 underneath.

So Way 2 solves the “hard to read at a glance” problem but not the “one measure per comparison” problem. If you need several comparisons (Actual vs Forecast, vs Budget, vs Prior Year, all filterable), you’re still maintaining a measure set for each.

3Way 3: Compare Without Writing Variance Measures

The third path skips the manual measure-per-comparison pattern. Instead of writing Variance and Variance % for every scenario pair, you load Actual and Forecast as values and let the visual generate the comparison dynamically, based on whatever field a user drops into a comparison field well.

This is the approach Flexa Tables takes. It’s a certified Power BI custom visual — it lives in the same report canvas as any native visual. Rather than pre-building four separate measures, you bring Scenario into the Compare field, and Actual-vs-Forecast (or MoM, or YoY) variance renders automatically, including in the published Power BI Service report, without a developer adding a new column every time someone asks for a different comparison.

CONFIGURE

VarianceSimulations

Field List

Region

Product

Scenario

Compared ByClear

Scenario✕

Select CalculationsClear

✓ΔVar

ΔVar%

Ratio

LayoutsMatrixTablesBase Field:Actual ▾Measured Fields:Forecast ▾

#	Region▼	Actual▼	Forecast▼	ΔVar▼
1	Northeast	$412K	$380K	+$32K
2	Southwest	$298K	$350K	-$52K
3	Midwest	$220K	$215K	+$5K
	Total	$930K	$945K	-$15K

Flexa Tables

Total Rows: 3

Export dataChartConditionalManageAnalyticsPivot

What this trades off:

None of this means Way 1 and Way 2 are wrong — a small, fixed finance report is often better served by two or three well-named DAX measures than by a new visual dependency. Way 3 earns its keep when the comparison itself needs to change often, or when the people asking for a new variance column aren’t the people who can write DAX.

4Which One to Use

If you’re building your first Actual vs Forecast report, start with Way 1. Add Way 2’s visual treatment once the audience is executives who need a one-second read. Reach for Way 3 when the request stops being “build this report” and becomes “let us keep adding comparisons ourselves.”