How to Compare Two Data Sets in Power BI

Comparing two different sets of data is fundamental to good analysis, whether you're looking at sales targets versus actuals, campaign performance year-over-year, or budget against spending. In Power BI, you have several powerful ways to bring these datasets together for a clear comparison. This article will walk you through the most effective methods, from building a proper data model with DAX to merging your data directly in Power Query.

Choose Your Method: Understanding the Options

Before diving into the steps, it's helpful to know the main approaches for comparing datasets in Power BI. The best choice depends on how your data is structured and what you want to achieve.

• Data Modeling (Relationships & DAX): This is the most robust and flexible method. You keep your datasets in separate tables but link them through a common dimension, like a date or product table. This is ideal for ongoing reporting and complex analysis.
• Merging Queries (Power Query): This method involves joining your tables together column-by-column in Power Query to create one large, "wide" table. It’s useful for simpler, one-off analyses where you need data side-by-side at the row level.
• Appending Queries (Power Query): This method stacks two or more tables on top of each other. It's used when your columns are the same, and you want to compare categories, like sales from different stores or regions, in the same visual.

We'll cover each of these so you can confidently pick the right one for your task.

Method 1: The Gold Standard - Using Relationships and DAX

For most comparison scenarios, like targets vs. actuals, this is the best practice. It keeps your data model clean, efficient, and scalable. You'll create measures to perform calculations instead of manipulating the underlying tables directly.

Step 1: Get Your Data and Prepare the Model

First, you need to import your data. Let's imagine we have two simple Excel files: one with ActualSales and another with SalesTargets. For this method to work, both tables need a common column to link them, like a Date or ProductID.

A key principle in Power BI modeling is to have a centralized "dimension" table. Instead of having a date column in both your sales and targets tables, it's better to create a separate Date Table.

1. Import your ActualSales and SalesTargets tables into Power BI.
2. Create a Calendar table. You can do this quickly with DAX. Go to the Data view, select New Table from the ribbon, and enter this formula:
3. Go to the Model view (the icon with an ER diagram on the left). You should see your three tables: ActualSales, SalesTargets, and Calendar.
4. Create relationships by dragging the [Date] column from your Calendar table to the date column in your ActualSales table. Do the same for your SalesTargets table. Your model should now look like a star, with the Calendar table in the middle.

This structure is powerful because the Calendar table can now filter both your sales and targets tables at the same time.

Step 2: Write Your Comparison Measures with DAX

With the relationships in place, you can now write DAX (Data Analysis Expressions) measures to perform the comparison. Measures are formulas that perform calculations on your data.

1. Go back to the Report view and right-click on your ActualSales table and select New Measure.
2. Create a measure to sum up your sales:
3. Do the same for your targets. Right-click your SalesTargets table and select New Measure:
4. Create a measure to calculate the variance:
5. For the variance as a percentage, create one more measure:

Step 3: Visualize the Comparison

The beauty of this method is the flexibility in visualization. You can now pull everything together into a report.

• Create a Matrix visual.
• Drag Year and Month from your Calendar table into the Rows field.
• Drag Total Sales, Total Target, Sales Variance, and Sales Variance % into the Values field.

Instantly, you have a perfectly formatted table comparing your performance by month and year. You can take this further with visuals like a Clustered Column Chart, placing the Month on the X-axis and Total Sales and Total Target on the Y-axis for an intuitive side-by-side view.

Method 2: Side-by-Side - Merging Queries in Power Query

Sometimes, you just want to combine two tables into one flat table, adding columns from one to the other. This is where merging comes in handy. It’s done in the Power Query Editor before the data is even loaded into your Power BI model.

This method is great when tables have a row-by-row relationship, but it's generally less efficient for very large datasets than the data modeling approach.

Step 1: Open Power Query and Select Your Tables

1. On the Power BI home ribbon, click Transform data to open the Power Query Editor.
2. You'll see your queries (tables) listed on the left pane. Let's assume you have a Sales table and a Targets table, and both have a Region column you want to join on.
3. Select the Sales query. In the Home ribbon, click the drop-down for Merge Queries and select Merge Queries as New. This creates a new, combined query without modifying your original ones.

Step 2: Configure the Merge

A new window will appear where you define how the tables should be joined.

1. The top dropdown should have Sales selected. Select the Targets table in the second dropdown.
2. Click on the common column in each table to select them as the key. For example, click the Region column in both tables. Power Query will show how many rows match.
3. For the Join Kind, select Full Outer. This ensures you see all rows from both tables, even if one is missing from the other (e.g., a region had sales but no target, or vice versa). Click OK.

Step 3: Expand the New Column and Visualize

You now have a new query with a column called Targets that contains table data. You need to expand it.

1. Click the expand icon (two arrows pointing in opposite directions) on the new Targets column header.
2. Uncheck all columns except the one you need, like TargetAmount. Also, uncheck the "Use original column name as prefix" box for a cleaner name. Click OK.
3. Create a custom column for the variance directly in Power Query. Go to the Add Column tab, click Custom Column, and use a simple formula:
4. Click Close & Apply in the Home ribbon.

Your new merged table is now available in the Report view, ready for you to build visuals just like with any other table.

Method 3: Stacking Data - Appending Queries

What if your datasets don’t link row-by-row, but are structurally similar and you want to compare their overall performance? For example, comparing sales from Store_A versus Store_B. In this case, you Append them - stacking them into one long table.

For this to work well, the tables should have matching column names.

Step 1: Add a Source Column

This is the most critical step. Before you append, you must add a column to each table to identify where the data came from. Otherwise, you'll have one big pile of data with no way to tell which rows belong to Store A or B.

1. In Power Query Editor, select your Store A Sales table.
2. Go to the Add Column tab and click Custom Column.
3. Name the new column Store Name and in the formula box, simply type "Store A". Click OK.
4. Repeat this process for your Store B Sales table, but use the text "Store B" in the formula.

Step 2: Append the Tables

1. In the Home ribbon, click the Append Queries dropdown and select Append Queries as New.
2. In the popup window, select your Store A Sales as the primary table and Store B Sales as the table to append. Click OK.

Step 3: Visualize the Stacked Data

Click Close & Apply. You now have a new query (Append1 by default, which you should rename to All Store Sales) containing the data from both stores. The magic is in that Store Name column you created.

• Create a Line Chart visual.
• Drag the Sale Date to the X-axis.
• Drag the Sale Amount to the Y-axis.
• Drag your Store Name column to the Legend field.

This creates two separate lines on the chart — one for Store A and one for Store B — allowing for a direct visual comparison of their performance over time.

Final Thoughts

Comparing datasets is a necessity for gaining real insights, and Power BI provides several great tools to get the job done. The key is choosing the right method: building a flexible data model with DAX is incredibly powerful for ongoing reports, while Merging or Appending data in Power Query offers a quick way to combine data for more straightforward analysis.

Of course, even with Power BI's capabilities, setting up data models, understanding join kinds, and writing DAX can be time-consuming, especially when you have data from multiple marketing or sales platforms. To completely remove that friction, we designed Graphed. Instead of manually building these comparisons, you can simply connect your data sources and ask questions like, "Create a dashboard showing our ad spend versus sales revenue for the last 30 days," and instantly get an interactive report without touching a single line of code.