Power BI Measure Subtract Two Columns | Add Two Measures in Power BI

Power BI is a great tool for data analysis. When people first learn DAX, they use it for basic calculations like addition and subtraction.

In this tutorial, we’ll learn how to subtract using the subtract function and how to add two measures together in Power BI.

In addition to that, we’ll explore several other examples of using the SUM and subtract functions in Power BI measures.

Power BI Measure Subtract Two Columns

Suppose you want to analyze a business’s sales performance and compare the actual sales made to the target sales.

Let’s say you have a SharePoint list where you have three columns in your data: Date, Actual Sales and Target Sales.

If you want to see whether you’ve exceeded or fallen short of your sales target in real-time, you can use Power BI to create a measure that subtracts the “Target Sales” from the “Actual Sales” using the steps below.

1. Open Power BI Desktop and load the above SharePoint List in Power BI Desktop. Then, you can see the data presented in the Data pane.

2. Then, under the Modeling tab, click “New measure“.

3. Then, in the formula bar, put below measure the click Commit button.

SalesVariance = SUM('Sales Performance'[Target Sales]) - SUM('Sales Performance'[Actual Sales]) 

Where:

• SalesVariance = This is a term we’re defining, which represents the difference between two other values.
• SUM(‘Sales Performance'[Target Sales]) = This calculates the total of all the “Target Sales” values from the “Sales Performance” table.
• SUM(‘Sales Performance'[Actual Sales]) = This calculates the total of all the “Actual Sales” values from the “Sales Performance” table.

4. After creating a table visual, add the Date, Actual Sales, and SalesVariance columns.

You can use this table to see how much your business’s actual sales differ from the target sales.

Power BI Minus Measure

A retail store wants to analyze its daily sales to understand how much revenue is generated from sales after accounting for returned items.

Here, we need to calculate the Net Sales Amount, which represents the actual revenue after subtracting the returns from the total sales.

In Power BI, we have two tables: the Sales Table, which contains the Date and Sales Amount, and the Returns Table, which contains the Date and Returns Amount.

First, we need to create a relationship between the two tables using the Date column. Follow these steps:

1. Go to the “Model” view by clicking on the “Model” icon on the left sidebar.
2. You will see the Sales Table and the Returns Table.
3. Click and drag the Date column from the Sales Table to the Date column in the Returns Table.
4. A line will appear between the two tables, indicating that a relationship has been created.

After that, create a measure using the DAX expression below:

Net Sales Amount = SUM('Sales Table'[Sales Amount]) - SUM('Returns Table'[Returns Amount])

Where:

• Net Sales Amount = This is what we’re calculating, which represents the final amount of sales after considering returns.
• SUM(‘Sales Table'[Sales Amount]) = This calculates the total sales amount from the “Sales Table”.
• SUM(‘Returns Table'[Returns Amount]) = This calculates the total returns amount from the “Returns Table”.

After creating a table visual, add the Date, Sales Amount, Returns Amount, and Net Sales Amount columns.

This use of a minus measure in Power BI helps the business clearly see its net sales, enabling better decision-making.

Power BI Subtract Two Measures

Suppose you want to analyze its profit margins to understand the profitability of its products.

Here, we have a Sales table with 10 rows representing different products along with their revenues and the cost of goods sold (COGS) for each product.

Now, we calculate the Profit Margin by subtracting the COGS from the Revenue and then dividing it by the Revenue.

First, we create a Total Revenue measure, then a Total COGS measure, and finally, a Profit Margin measure.

To calculate the Total Revenue measure in Power BI, you can use the following DAX expression:

Total Revenue = SUM('Product Sales Costs'[Revenue])

To calculate the Total COGS measure in Power BI, you can use the following DAX expression:

Total COGS = SUM('Product Sales Costs'[COGS])

To calculate Profit Margin measure in Power BI, you can use the following DAX expression:

Profit Margin = DIVIDE([Total Revenue] - [Total COGS], [Total Revenue])

Then, create a table visual and add the Total Revenue, Total COGS, and Profit Margin columns.

This way, you can create Power BI by subtracting two measures.

Power BI DAX Subtract

Let’s say you want to monitor the progress of your development team’s tasks. You have a dataset in Power BI that shows when each task starts and ends.

Now follow the below steps to find the time taken for each task:

1. After loading the dataset into Power BI, navigate to the table view and click on “New column.”

2. In the formula bar, put below the DAX expression. Then click the Commit button.

Time Taken = FORMAT([End Time] - [Start Time], "hh:mm:ss")

Where:

• Time Taken = This is what we’re calculating, representing the duration of time between two events.
• [End Time] – [Start Time] = This calculates the difference between the “End Time” and the “Start Time”, giving us the time elapsed.
• FORMAT(…, “hh:mm:ss”) = This formats the calculated time difference into hours, minutes, and seconds for easier reading.

3. Then, you can see a new column added in the table view.

This way you can subtract two columns in Power BI.

Add Two Measures in Power BI

Let’s say you’re managing a social media platform. Your platform tracks two important metrics: the number of new users joining each month and the number of posts made by all users in that same month. You want to analyze the overall engagement level by combining these two metrics.

Here, I loaded a simple example dataset with 12 rows of data representing the number of new users joining each month and the number of posts made by all users in that same month. Here’s how the dataset might look like:

Then, under the Home tab, click New measure, and in the formula bar, put below the DAX expression:

NewUsers = SUM('Engagement Metrics'[New Users])

To calculate the TotalPosts, you can count the number of rows in your dataset. Here’s the DAX expression to achieve this:

TotalPosts = SUM('Engagement Metrics'[Total Posts])

To add these two measures together, create a new measure using the DAX expression below:

CombinedEngagement = [NewUsers] + [TotalPosts]

Now, you can add these measures to your visualizations. For instance, you could create a column chart with the x-axis representing different months and the y-axis representing the CombinedEngagement.

By following these steps, you can effectively add two measures together in Power BI.

Combine Two Measures in Power BI

Imagine you are managing a customer support center. You want to combine two measures: the average number of tickets resolved by each agent (Productivity) and the average satisfaction score given by customers for each agent (Satisfaction).

By combining these measures, you aim to identify high-performing agents who are also maintaining high customer satisfaction.

In Power BI, we have two tables: “Tickets Resolved” with the columns AgentID and Resolved Tickets, and “Satisfaction Scores” with the columns AgentID and Satisfaction Score.

Now, we need to create a relationship between the two tables using the AgentID column. Follow these steps:

1. Go to the “Model” view by clicking on the “Model” icon on the left sidebar.
2. You will see the Tickets Resolved table and the Satisfaction Scores table.
3. Click and drag the AgentID column from the Tickets Resolved table to the AgentID column in the Satisfaction Scores table.
4. A line will appear between the two tables, indicating that a relationship has been created.

Now Select the Tickets Resolved table and create a measure using the below DAX formula:

AverageTicketsResolved = AVERAGE('Tickets Resolved'[Resolved Tickets])

Then, select the Satisfaction Scores table and create a measure using the below DAX formula:

AverageSatisfactionScore = AVERAGE('Satisfaction Scores'[Satisfaction Score])

To combine the two measures, enter the following DAX expression in the formula bar:

CombinedMeasure = [AverageTicketsResolved] + [AverageSatisfactionScore]

Create a column chart using AgentID and CombinedMeasure to find agents with high productivity and satisfaction scores.

This way, you can combine two measures in Power BI.

Additionally, you may like some articles below:

• How to Compare Two Columns in Different Tables in Power BI
• Power BI Matrix Multiple Columns Examples
• username() and userprincipalname() in Power BI
• Convert Month Number to Month Name in Power BI
• Embed Power BI Report in SharePoint Online

In this article, we covered various methods for performing calculations involving subtraction in Power BI using DAX (Data Analysis Expressions). Specifically, we discussed how to subtract two columns, create a measure for subtraction, utilize the minus operator, subtract two measures, add two measures, and combine measures in Power BI.