How to Show Planned vs Actual in Power BI

Comparing planned outcomes against actual results is one of the most fundamental tasks in business analysis. Whether you're tracking sales targets, marketing budgets, or project timelines, this comparison tells you where you're winning and where you need to adjust course. This guide gives you clear, step-by-step instructions for creating powerful "planned vs. actual" visuals in Power BI, turning your raw data into actionable insights.

Why Compare Planned vs. Actual Data?

Before jumping into Power BI, it helps to understand why this type of analysis is so valuable. At its core, comparing your plan to your results helps you:

• Measure Performance: Are you hitting your goals? By how much? This is the most basic and critical question a variance analysis answers.
• Identify Variances Quickly: Pinpoint exactly where and when you deviated from the plan, allowing you to ask why. Did a specific sales region overperform? Did a marketing campaign go over budget?
• Improve Forecasting: Consistently missing your targets might mean your initial plans were unrealistic. This feedback loop helps you create more accurate forecasts and budgets in the future.
• Foster Accountability: When teams have clear targets and see their actual performance tracked against them, it creates a culture of ownership and accountability.

Preparing Your Data for Power BI

Clean, well-structured data is the foundation of any good report. Before you can visualize anything, your data needs to be in a format Power BI can understand. For a planned vs. actual analysis, you'll typically need two separate tables: one for your actuals (e.g., sales transactions) and one for your planned figures (e.g., monthly sales targets).

Ideally, your data should look something like this:

Actuals Table (e.g., 'Sales Data')

This table contains your day-to-day results. The essential columns are a date and a value.

• TransactionDate: The specific date the sale occurred (e.g., 2023-01-15).
• SalesAmount: The value of the sale (e.g., 500).
• Region: A category for slicing the data (e.g., 'North').

Planned/Targets Table (e.g., 'Sales Targets')

This table contains your goals. Note that targets are often set at a higher level (e.g., monthly) than your actuals (e.g., daily).

• MonthDate: The first day of the month for the target (e.g., 2023-01-01).
• TargetAmount: The budgeted or planned value (e.g., 50,000).
• Region: The same category as in your Actuals table (e.g., 'North').

The Most Important Step: Creating a Date Table

To make these two tables work together, you need a central calendar or "Date" table. A date table is a standalone table that contains a continuous list of every date in your analytical range. You then create relationships from this central date table to your 'Sales Data' table and your 'Sales Targets' table.

This structure fixes a common problem: your actuals data is daily, but your targets are monthly. The date table acts as a bridge, allowing Power BI to correctly aggregate your daily sales to compare them against monthly targets.

You can create a date table in Power BI using DAX with functions like CALENDAR() or CALENDARAUTO().

Method 1: The Classic Column Chart

The simplest way to visualize planned vs. actual values is with a clustered column chart. This approach is straightforward and easy for anyone to understand at a glance.

Step 1: Get Your Data and Model Set Up

First, load your tables (Actuals, Planned, and your Date table) into Power BI Desktop. Go to the "Model view" and create a relationship from the Date column in your date table to the date columns in both your actuals and planned tables. Usually, you'll drag the Date field from the Date Table onto the TransactionDate in your Sales Data table and the MonthDate in your Sales Targets table.

Step 2: Create Your Measures

Instead of dragging raw columns into your charts, it's best practice to create explicit DAX measures. This makes your formulas reusable and your model more robust.

Go to the Report view, select the 'Home' tab, and click 'New Measure.' Create two basic measures:

• Total Actuals = SUM('Sales Data'[SalesAmount])
• Total Planned = SUM('Sales Targets'[TargetAmount])

Step 3: Build the Clustered Column Chart

Now, let's build the visual.

1. In the 'Report' view, click the Clustered column chart icon in the Visualizations pane.
2. Drag the appropriate field from your Date Table onto the X-axis (e.g., 'Month').
3. Drag your new measures, [Total Actuals] and [Total Planned], into the Y-axis field.

You'll now have a chart with two bars for each month - one showing your actual results and the other showing your planned target. It's a clear, side-by-side comparison of your performance over time.

Method 2: The Line and Clustered Column Chart

While the clustered column chart works well, a combo chart can be more intuitive. By representing the "Planned" value as a line, you signal that it's a target or a guiding metric, while the bars represent your concrete, "Actual" performance.

Step 1: Choose the Combo Chart Visual

Select a blank area on your report canvas and click the Line and clustered column chart icon from the Visualizations pane.

Step 2: Configure the Chart Fields

The setup for this chart is slightly different. You will use different fields for the columns and the line.

1. Drag your date field (e.g., 'Month' from your Date Table) to the X-axis field.
2. Drag your [Total Actuals] measure to the Column Y-axis field.
3. Drag your [Total Planned] measure to the Line Y-axis field.

This visual makes it incredibly easy to see if you are above or below the target line in any given month. Areas where the top of the bar is above the line show you've exceeded your plan, while any bars below the line indicate a shortfall.

Pro Tip: Go to the 'Format visual' pane, find the 'Lines' section, and change the style of the target line to a 'Dashed' line to further differentiate it visually from the solid bars.

Step 3: Calculating and Visualizing Your Variance

Seeing planned versus actual side-by-side is great, but the real insight comes from calculating the variance - the difference between the two. This tells you the magnitude of your over- or under-performance.

Step 1: Create a Variance Measure with DAX

Creating a variance calculation is simple once you have your base measures for actuals and planned. Create another new measure with the following DAX formula:

Absolute Variance

This shows the difference in direct value (e.g., dollars).

Variance Absolute = [Total Actuals] - [Total Planned]

Percentage Variance

This shows the difference as a percentage of the plan, which is often better for comparison.

Variance % = DIVIDE([Variance Absolute], [Total Planned])

After creating the Percentage Variance measure, make sure to click on it in the Fields pane and change its format to "Percentage" in the Measure tools ribbon.

Step 2: Add Conditional Formatting for Impact

Now, let's use this new [Variance Absolute] measure to add another layer of insight to our existing column chart. The goal is to color the actuals bars red if they're under plan and green if they're over plan.

1. Select your combo chart visual from Method 2.
2. Go to the "Format your visual" pane.
3. Expand the "Columns" section and find the "Colors" subsection.
4. Click the fx button next to the color property. This opens the conditional formatting dialog.
5. In the dialog box, set it up as follows:
6. Click OK.

Your chart's columns will now automatically change color based on their performance against the plan, giving your audience an immediate visual cue about what's working and what isn't.

Final Thoughts

As you can see, Power BI provides several powerful and flexible methods to visualize and analyze your planned versus actual data. From simple clustered columns for easy comparison to dynamic combo charts with color-coded variances, you can build impactful reports that clearly communicate performance and guide better business decisions.

If you've ever spent hours wrestling with DAX, data models, or formatting options just to create these comparisons, you know how much time it can take. We created Graphed to remove this friction entirely. Instead of configuring fields and setting up conditional formatting rules manually, you simply connect your data sources and ask questions in plain English, like "Show me a chart of actual sales vs target sales for each month this year, and color the bars based on the variance." Graphed instantly builds the exact, real-time-updated visual you need, letting you get straight to the insights.