How to Calculate Percentage of Two Columns in Power BI (DAX, Variance %, Visual Calc)

Our sales manager, Adele Vance, asked me a simple question last week: which regions hit their 2026 sales target, and by how much? The numbers sat in two different columns, actual sales in one file and the target in another.

In this tutorial, we’ll work through how to power bi calculate percentage of two columns the right way, with a DAX measure that divides one total by the other. Then we’ll get the percentage difference (variance %), add a visual calculation for the plain difference, and look at the calculated column that gives a wrong total.

Quick answer: Create two base measures, Total Sales = SUM(Sales[Sales]) and Total Target = SUM(Targets[Target]), then % of Target = DIVIDE([Total Sales], [Total Target]) and set its format to Percentage. For the percentage difference, use DIVIDE([Total Sales] - [Total Target], [Total Target]). Always use measures, not calculated columns, so the total row divides totals instead of adding up row percentages.

The Sample Data: Sales and Targets in Two Tables

Everything below comes from one Power BI Desktop report I built on 10/28/2026. If you’re new to the tool, our guide on creating a report in Power BI Desktop walks through the basics first.

My demo files are three CSV files:

File	What’s in it	Rows
SPGuides_Sales_2026.csv	OrderID, OrderDate, Region, City, State, ZIP, Category, Product, SalesRep, Quantity, Sales	60 orders, January to September 2026
SPGuides_Region_Targets_2026.csv	Region, Target	4 regions
SPGuides_Sales_Reps_2026.csv	SalesRep, Email, Title	5 reps (used for security posts, not needed here)

The targets file is tiny, so you can type it yourself. Here it is with the actual sales for each region from the orders file:

Region	Target	Actual sales
East	$75,000	$81,686
West	$70,000	$63,783
South	$15,000	$16,813
Central	$5,000	$3,419
Total	$165,000	$165,701

So the two columns we want to compare are Sales (in the Sales table, one row per order) and Target (in the Targets table, one row per region). That’s the most common real-world setup: actuals and goals almost never live in the same table.

To load each file, go to Home > Get data and pick Text/CSV from the Common data sources list.

Browse to the file and Power BI shows a preview. For the targets file, you should see Region and Target with four rows. Select Load.

Notice the Target values in the preview: 75000, 70000, 15000 and 5000. Power BI detected them as numbers, which matters later because SUM only works on numeric columns.

Connect the Two Tables With a Relationship

A ratio of two columns from two tables only works per region if Power BI knows how the tables connect. Open Model view (the third icon in the left rail) and check the line between Sales and Targets.

In my file, Power BI created both relationships automatically after the load: Sales to Targets on Region, and Sales to Sales Reps on SalesRep. The * end sits on Sales (many orders per region) and the 1 end on Targets (one row per region).

Select the line to check its properties in the right pane:

You want Cardinality Many to one (*:1), the relationship marked active, and Cross-filter direction Single. If no line appears, drag Region from Sales onto Region in Targets. For other ways to line up two tables, see comparing two columns in different tables in Power BI.

Why it matters: when the table visual groups by Targets[Region], the relationship passes that filter down to Sales, so each row’s Total Sales only counts that region’s orders.

Calculate the Percentage of Two Columns With a DAX Measure

Now the actual math. A measure is the right tool because it calculates after the visual filters the data, so it works on any row, total or slicer selection. If you want the background, our post on the difference between a calculated column and a measure explains it in detail.

Start with two base measures. Select the Sales table, then New measure in the Calculations group of the Home ribbon:

Enter the first measure, press Enter, then create the second one the same way (I put both in the Sales table and set them to Currency):

Total Sales = SUM(Sales[Sales])
Total Target = SUM(Targets[Target])

Now the percentage itself. Create one more measure:

% of Target = DIVIDE([Total Sales], [Total Target])

Then, with the measure still selected, go to Measure tools > Formatting, click the % button (or set Format to Percentage) and set the decimal places to 2.

I built a table visual named Sales vs Target by Region 2026 with Targets[Region], Total Sales, Total Target and % of Target. The results: Central 68.38%, East 108.91%, South 112.09% and West 91.12%.

Look at the Total row: $165,701 / $165,000 = 100.42%. That’s the real company-wide ratio, because the measure divides the two totals. It doesn’t add or average the four region percentages.

Important: a new measure is created as a Decimal number. Until I clicked %, East showed 1.09 instead of 108.91%. Don’t multiply by 100 in the formula to fix that, or the percentage format will show 10,891%.

Why DIVIDE instead of the / operator? Microsoft documents that DIVIDE returns BLANK (or an alternate result you pass as the third argument) when the denominator is 0. With a plain slash, a region with no target would show Infinity or NaN. Our Power BI NaN error post covers that symptom.

Percentage Difference Between Two Columns (Variance %)

The next question Adele asked was “how far above or below target is each region?” That’s the percentage difference between two columns, usually called variance %. The formula is (actual – target) / target.

Variance % = DIVIDE([Total Sales] - [Total Target], [Total Target])

Format it as a percentage with 2 decimals, the same way, and add it to the table:

Central shows -31.62%, East 8.91%, South 12.09% and West -8.88%. The Total row reads 0.42%, so the company as a whole beat its $165,000 target by a hair.

A negative number means the region is below target. East’s $81,686 against $75,000 is $6,686 over, and $6,686 / $75,000 = 8.91%. You can check every row the same way.

Notice that Variance % is always % of Target minus 100%. East is 108.91% of target, so it’s 8.91% over. Pick whichever one your audience reads more easily; finance teams usually want the variance.

If the formula gets longer, variables make it easier to read. This version returns the same results:

Variance % (VAR) =
VAR Actual = [Total Sales]
VAR Goal = [Total Target]
RETURN
    DIVIDE(Actual - Goal, Goal)

Percentage Change vs Symmetric Percentage Difference

Variance % divides by the target, so the target is the baseline. That’s what you want for actual vs target, budget vs spend, or this year vs last year (percentage change).

Some people search “percentage difference” when there’s no baseline, for example two warehouses’ counts. The textbook formula then divides the absolute difference by the average of the two values:

% Difference (symmetric) =
VAR A = [Total Sales]
VAR B = [Total Target]
RETURN
    DIVIDE(ABS(A - B), (A + B) / 2)

I’d only use this when neither column is the “expected” value. For targets, keep using Variance %, because “12.09% over target” is what people expect to read.

To color negative variances red and positive ones green, see conditional formatting for positive and negative numbers in Power BI.

Visual Calculation: Difference Between Two Columns Without a Measure

Sometimes you just want the plain difference, in dollars, for one table, and you don’t want another measure in the model. That’s what visual calculations are for. They’re DAX formulas that live on a single visual and use the values it already shows.

Here’s how I added one to the Sales vs Target table:

1. Select the table visual.
2. On the Home ribbon, select New visual calculation (the button next to New measure). Edit mode opens with a formula bar under the visual.
3. Enter the formula below and press Enter.
4. Select Back to report at the top left.

Sales minus Target = [Total Sales] - [Total Target]

The new column shows -1,581.00 for Central, 6,686.00 for East, 1,813.00 for South and -6,217.00 for West, with 701.00 on the Total row. Those match the actual-minus-target math from the sample table.

You don’t need SUM in a visual calculation. Microsoft’s docs explain that it works row by row on the visual’s data, like a calculated column, but on aggregated values. You also can’t use RELATED inside one.

The result shows as a plain decimal at first. To show it as currency, use Data format in the Format pane while you’re in edit mode. To change the formula later, right-click the calculation in the visual’s field list and select Edit calculation.

You could also write a percentage this way, DIVIDE([Total Sales], [Total Target]) as a visual calculation. The trade-off: Microsoft lists no reuse across visuals, no filtering or sorting on the calculation, and no pinning to dashboards. For a percentage that appears on cards and charts too, keep the measure.

Pro Tip: The lower half of the New visual calculation button opens templates such as Versus previous and Versus first. They’re handy for differences between rows rather than between two columns.

When Both Columns Are in the Same Table

The pattern doesn’t change when both columns sit in one table, for example Ordered Qty and Shipped Qty in an Orders table. Aggregate each column, then divide:

% Shipped = DIVIDE(SUM(Orders[Shipped Qty]), SUM(Orders[Ordered Qty]))

You don’t need a relationship here, because both columns are filtered by the same rows. For plain division without the percentage format, our post on dividing two columns in Power BI has more variations. And for subtraction-only measures, see subtracting two measures in Power BI.

If the target is stored in another table with no shared key, merge the tables in Power Query first or add a lookup column. Without a relationship, Total Target ignores the region on each row, so every region is divided by the full $165,000.

Why a Calculated Column Gives the Wrong Total

This is the mistake I see most often in community threads, so I reproduced it on purpose. I added a calculated column to the Sales table that divides each order by its region’s target:

Row % of Target = DIVIDE(Sales[Sales], RELATED(Targets[Target]))

Then I dropped it into the same table. Power BI summed it automatically (the column header reads Sum of Row % of Target):

The region rows look perfect: 68.38%, 108.91%, 112.09% and 91.12%, exactly like the measure. But the Total row says 380.50%, because it adds the four region ratios together instead of dividing $165,701 by $165,000.

The regions only matched because each region has a single target, so the per-order shares happen to add up. Any row above the region level, like the grand total, adds ratios that were never meant to be added. The fix is simple: ratios belong in measures. Keep calculated columns for row-level values you filter or group by.

If you really need a per-row percentage as a field, set its summarization to Don’t summarize (Column tools ribbon) so nobody totals it by accident. Our tutorial on adding a calculated column in Power BI covers the column basics.

Which Method Should You Use?

You need	Use	Result in my report
Percentage of one column vs another (actual vs target)	Measure: DIVIDE([Total Sales], [Total Target])	East 108.91%, Total 100.42%
Percentage difference / variance %	Measure: DIVIDE([Total Sales] – [Total Target], [Total Target])	East 8.91%, Total 0.42%
Plain difference on one table only	Visual calculation: [Total Sales] – [Total Target]	East 6,686.00, Total 701.00
Difference with no baseline	Symmetric formula: ABS(A – B) / ((A + B) / 2)	Not used for targets
A per-row ratio summed in a visual	Avoid (calculated column)	Total 380.50% (wrong)

My default is the measure pair: % of Target plus Variance %. They work in tables, matrices, cards and charts, and they respect slicers.

If you also need each region’s share of all sales, that’s a different calculation, covered in Power BI percentage of total by category. For shares inside a hierarchy, see calculating the percentage of rows in Power BI.

Common Errors and How to Fix Them

• The measure shows 1.09 instead of 108.91%: it’s still a Decimal number. Select it and click % on the Measure tools ribbon.
• Every region shows the same target: the tables aren’t related, so Total Target ignores the region. Check Model view for an active *:1 line between them.
• The Total row looks too high (like 380.50%): you summed a calculated column. Replace it with a DIVIDE measure.
• Infinity or NaN: you used / and a target is 0 or blank. Switch to DIVIDE, which returns BLANK.
• The percentage is upside down: the numerator and denominator are swapped. For % of target, actual goes on top; for variance %, divide by the target, not by the actual.
• SUM returns an error: the column came in as text. Change its data type to a number in Power Query.

The old version of this post divided the difference by the actual value instead of the target, which is the last mistake on that list. If you copied that formula before, swap the denominator. For more DAX patterns like these, browse our Power BI tutorials and the list of Power BI DAX functions with examples.

These posts build on the same kind of table, so they’re a good next read:

• How to Create Table Visual in Power BI?
• Power BI Table Conditional Formatting [With Examples]
• 6 Various Power BI Matrix Multiple Columns Examples
• How to Merge Tables in Power BI

Frequently Asked Questions