How to Combine Two Measures in Power BI

Combining two measures in Power BI is how you turn basic data points into meaningful business metrics. Instead of just looking at sales and costs separately, you can combine them to create a completely new metric like profit. This article will walk you through the most common ways to combine measures, starting with simple math and moving on to more powerful functions.

Why Combine Measures in Power BI?

Before jumping into the "how," let's quickly cover the "why." You combine measures to create new Key Performance Indicators (KPIs) and add deeper context to your reports. It’s the difference between having raw ingredients and a fully prepared meal, combining measures helps you tell a complete story with your data.

Here are a few common scenarios where combining measures is essential:

• Calculating Profitability: You have a measure for [Total Sales] and another for [Total Costs]. Combining them gives you [Total Profit], arguably one of the most important metrics for any business.
• Finding a Conversion Rate: By combining [Total Conversions] and [Total Website Clicks], you can calculate your website's conversion rate to see how effective your marketing funnel is.
• Measuring Performance Against Targets: You can compare [Actual Revenue] against [Revenue Target] to calculate variance and immediately see if you're on track to hit your goals.

In short, combining measures moves you from simple reporting (what happened) to true analysis (what this means for the business).

Method 1: Using Basic Math Operators Directly in a New Measure

The most straightforward way to combine measures is by using standard mathematical operators right inside a new DAX formula. If you’ve ever written a formula in Excel, this will feel very familiar.

The basic operators you’ll use are:

• + for Addition
• - for Subtraction
• * for Multiplication
• / for Division

The syntax is simple: you create a new measure and define it as a calculation between your existing measures.

Practical Example: Calculating Total Profit

Let's say you have a sales table in your Power BI model. You've already created two basic measures:

• One for total revenue: Total Sales = SUM(Sales[Revenue])
• And another for the total cost of goods sold: Total COGS = SUM(Sales[Cost])

Now, let's combine them to create a new measure called Total Profit.

Step-by-Step Instructions:

1. In the Fields pane on the right side of Power BI, right-click on the table where you want to store your new measure (or your dedicated measures table).
2. Select New measure.
3. The formula bar will appear at the top. Here, you'll type your DAX formula. Name the new measure "Total Profit" and use the subtraction operator to combine your two existing measures.
4. Enter the following DAX formula:
5. Press Enter. Your new measure, Total Profit, now appears in your fields list with a small calculator icon next to it.

You can now drag this Total Profit measure onto any visual in your report — a card, a table, or a chart — to instantly see your profitability. The best part is that this measure is dynamic. If you apply a filter (for example, filtering by a specific product category or date range), the Total Sales, Total COGS, and your new Total Profit measures will all recalculate automatically.

Method 2: Safely Dividing Measures with the DIVIDE() Function

While you can use the forward slash (/) for division, there’s a much safer and cleaner way: the DIVIDE() function. Why is it safer? Because it automatically handles cases where you might be dividing by zero, which would otherwise return an error or an "Infinity" value that messes up your visuals.

The DIVIDE() function takes two required arguments and one optional one:

DIVIDE(<numerator>, <denominator>, [<alternate_result>])

• numerator: The measure you want to divide (the number on top).
• denominator: The measure you're dividing by (the number on the bottom).
• alternate_result (optional): The value you want to return if the denominator is zero. If you leave this blank, it will return BLANK(). Most of the time, you'll want to return 0.

Practical Example: Calculating Conversion Rate

Imagine you run an e-commerce store and are tracking ad performance. You have measures for [Total Clicks] and [Total Conversions].

Your goal is to calculate the conversion rate. The formula is simple: Conversions / Clicks. But what happens if a new campaign has zero clicks on its first day? If you use the standard / operator, your report will show an error.

Let's build a robust conversion rate measure using DIVIDE():

Step-by-step:

1. Create a New measure.
2. Enter the following DAX formula:
3. Press Enter.
4. Since this is a rate, you’ll want to format it as a percentage. Select the new Conversion Rate measure in the Fields pane, then go to the Measure tools tab in the ribbon and change the format to Percentage.

Now, if you have a campaign with zero clicks, this measure will safely return 0% instead of an error, keeping your reports clean and accurate.

Method 3: Combining Measures with Conditional Logic

Sometimes you need more than just direct math. You need to combine measures based on certain rules or conditions. This is where logical functions like IF() and SWITCH() come in handy. They allow you to create dynamic measures that return different results based on whether a condition is met.

Combining Measures with IF()

The IF() function checks whether a condition is true and returns one value if it is, and another value if it's false. The syntax is:

IF(<logical_test>, <value_if_true>, <value_if_false>)

Let's use it to calculate a sales bonus. Imagine the rule is: if sales are greater than the target, a bonus is paid, otherwise, no bonus is given.

Assuming you already have measures for [Total Sales] and [Sales Target]:

1. Create a New measure called "Bonus Status".
2. Enter this formula:

This creates a text-based result you can use in tables. You can also use measures as the output. For example, if you want to calculate the actual bonus amount (let's say it's 5% of sales over target):

Bonus Amount = IF([Total Sales] > [Sales Target], ([Total Sales] - [Sales Target]) * 0.05, 0)

This formula first checks if sales have exceeded the target. If they have, it calculates the amount over target and multiplies it by 5%. If not, it returns 0.

Leveling Up with SWITCH() for Multiple Conditions

What if you have more than two conditions? You could nest several IF() statements, but that gets messy fast. A much cleaner solution is the SWITCH() function.

Let's say you want to categorize sales performance into "Excellent," "Good," and "Needs Improvement" based on how close it is to the target.

• Excellent: Over 100% of the target.
• Good: Between 80% and 100% of the target.
• Needs Improvement: Below 80% of the target.

First, create a helper measure to calculate the percentage of the target achieved:

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

Now, use SWITCH() to create the performance category based on the result of that measure:

1. Create another New measure.
2. Enter this formula:

Performance Category = 
SWITCH(
    TRUE(),
    [% of Target] > 1, "Excellent",
    [% of Target] >= 0.8, "Good",
    "Needs Improvement"
)

The SWITCH(TRUE(), ... ) pattern works by evaluating each condition in order. The first one that returns TRUE determines the result. It's a highly readable way to handle complex conditional logic for your measures.

Best Practices for Combining Measures

As you build more complex reports, keeping your work organized is crucial. Here are a few tips:

• Name Measures Clearly: Ad Spend vs GA Revenue Variance is much more helpful than Measure 3. Good naming makes your reports easier for others (and a future you!) to understand.
• Use a Measure Table: To avoid cluttering your data tables, you can create a dedicated table just to hold all your measures. Go to the Home tab, click Enter Data, create a one-column table, and name it something like "_Measures". You can then move all your measures there to keep them in one place.
• Add Comments to Your DAX: For complex formulas, you can add comments to explain your logic. Use // for a single-line comment or /* ... */ for multi-line comments.
• Test Your Measures: After creating a new measure, drop it into a simple table or card visual to make sure it's calculating correctly before using it in a more complex chart.

Final Thoughts

Combining measures is a fundamental skill in Power BI that lets you transform columns of raw data into valuable business insights. Whether you're using simple arithmetic, the safe DIVIDE() function for rates and ratios, or conditional logic like IF() and SWITCH(), you'll gain a new level of analytical power for your dashboards and reports.

While mastering DAX can be powerful, we know that manually building reports and writing formulas can pull you away from what matters most: acting on insights. That’s why we created Graphed. We connect directly to your marketing and sales data sources so you can get answers instantly using simple, natural language. Instead of figuring out the right DAX, just ask, "Show me my conversion rate by campaign for last month," and Graphed builds the report for you in seconds.