How to Subtract in Power BI Using DAX?

Power BI is a great tool for data analysis. When people first learn Power BI DAX (Data Analysis Expressions), they often begin with basic arithmetic calculations like addition, subtraction, multiplication, and division.

In this tutorial, I will demonstrate how to subtract in Power BI using DAX (Calculate column, Measure).

We will also discuss how to Subtract a column from a Measure in Power BI and subtract values from different tables in Power BI with various scenarios.

Power BI Subtraction DAX

In Power BI, DAX (Data Analysis Expressions) is a language for creating formulas and expressions to manipulate data. When you subtract in DAX, you’re taking one value away from another.

For example, you have a dataset with sales figures for different products. You might want to subtract the sales of one product from another to see the difference in sales between them. You can use a subtraction DAX expression to do this calculation.

The syntax of subtraction is:

SubtractValues = Value1 – Value2

How to Subtract in Power BI Using DAX

Now, I will tell you how to subtract two columns using Power BI DAX.

Scenario:

Let’s imagine you are running a small retail business, and you want to track your inventory. You have a dataset in Power BI that includes the initial quantity of items in stock and the quantity sold daily.

You want to calculate the amount of stock remaining.

In this scenario, we have an Excel file named “Inventory_Sales” with columns for Date, Initial Stock, and Units Sold. Check the screenshot below.

subtract dax in Power BI

Now follow the below steps on how to subtract using DAX:

See also  Power Query Create Table in Power BI + Examples

1. Open Power BI Desktop and load data using the Get data option. Then, you can see data in the Data pane.

Power BI minus in dax

2. Go to the Table view and click the New column under the Table tools.

Power BI dax formula for subtraction

3. In the formula bar, put the below expression. Then click Commit.

Remaining Stock = [Initial Stock] - [Units Sold]

Where:

  • Remaining Stock = Name of the New column
  • Initial Stock = Name of the column that stores Initial Stock
  • Units Sold = Name of the column that stores how many units sold in a day
power bi subtract two columns

4. After that, one column is created in the Table view.

power bi dax subtract two columns

This way, you subtract two columns in Power BI DAX to see how much stock remains after it is sold.

How to Subtract a Column from a Measure in Power BI

Let’s say you manage a sales team and want to track their performance against a target.

You have a dataset in Power BI that includes the sales made by each salesperson and a target sales amount.

power bi minus measure

Follow the below steps to see how to subtract in a measure in Power BI:

1. Open Power BI Desktop and load data using the Get data option. Then, you can see data in the Data pane.

How to Subtract Two Columns in Power BI measure

2. Under the Home tab, click “New measure“.

How to subtract a dax measure in Power BI

3. In the formula bar, Put the below expression. Then click Commit.

Variance = SUM('SalesPerformance'[Sales Amount]) - SUM('SalesPerformance'[Target Sales])

Where:

  • SUM(‘SalesPerformance'[Sales Amount]) = This formula calculates the total sales amount for all salespeople in the SalesPerformance table.
  • SUM(‘SalesPerformance'[Target Sales]) = This formula calculates the total target sales amount for all salespeople in the SalesPerformance table.
  • Variance = This store results in the variance between actual and target sales for all salespeople in the SalesPerformance table.
Subtract a constant from a measure in Power BI

4. After that, Under the Home tab, expand Visual gallery(black box) -> Click the Table visual.

Power BI Subtraction of Measures across different rows

5. Then, using the +Add data option, add Salesperson, Target Sales, Sales Amount, and Variance into the Column field.

Subtracting measure value from each row of a table in Power BI

6. you can see the table visual in the report view.

Simple subtraction in Power BI Measure

After that, you can track their performance against a target easily.

See also  Power BI Date Slicer By Month [How to Use]

Power BI Subtract Two Columns from Different Tables

In this example, you see how to Subtract values from different tables in Power BI.

Let’s say you are managing your business’s sales and expense data in separate tables and want to calculate the profit.

According to this scenario, we have an Excel file named RetailData that contains two tables: SalesData and ExpensesData.

Power BI dax subtract two columns from different tables

Now follow the below step to subtract two columns from different tables in Power BI:

1. Open Power BI Desktop and load two tables using the Get data option.

Subtraction in Power bi using DAX

2. you will see data from both tables in the Data pane.

power bi compare two columns in different table

3. Now, we calculate sales revenue. To do this, select the SlaesData table. Under the “Table tools” tab, click “New measure.”

power bi difference between two columns from different tables

4. In the formula bar, Put the below expression. Then click Commit.

Total Sales Revenue = SUM('SalesData'[Sales Amount])

Where:

  • Total Sales Revenue = This is the measure’s name that stores sales revenue.
  • SUM() = This is a function that calculates the sum of a column.
  • SalesData = This is the table name from which the sales amount column is present.
  • Sales Amount = This is the column name containing the sales value.
power bi calculate difference between two columns in different tables

5. Now, we calculate total expenses. To do this, select the ExpensesData table. Under the “Table tools” tab, click “New measure.”

how to subtract two columns in power bi

6. In the formula bar, Put the below expression. Then click Commit.

Total Expenses = SUM('ExpensesData'[Expense Amount])

Where:

  • Total Expenses = This is the measure’s name that stores total expenses.
  • SUM() = This is a function that calculates the sum of a column.
  • ExpensesData = This is the table name where the Expense Amount column is located.
  • Expense Amount = This is the column’s name containing the expense values.
how to subtract two columns in power bi dax

7. Now we calculate the Profit margin. To do this, under the Home tab, click “New measure.”

Power BI Subtract Two Columns different table

8. In the formula bar, Put the below expression. Then click Commit.

Profit = [Total Sales Revenue] - [Total Expenses]

Where:

  • Profit = Name of the new measure.
  • [Total Sales Revenue] = The measure’s name storing the total revenue.
  • [Total Expenses] = The measure storing the total expenses.
How to subtract two columns in Power BI reports

9. After that, Under the Home tab, expand Visual gallery(black box) -> Click the Table visual.

How to Subtract in Power BI

10. Then, using the +Add data option, add Product Category, Total Sales Revenue, Total Expenses, and Profit into the Column field.

power bi subtract two measures from different tables

11. After that you can see in the table visual.

Power BI Subtract Two Columns from Different Tables

This is how you calculate the difference between two columns in different tables in Power BI.

See also  Power Query Create Table in Power BI + Examples

Some more Power BI articles you may also like:

Analyzing this data in real-time allows you to make informed decisions to optimize your business operations, allocate resources efficiently, and maximize profits.

I hope this article will be very helpful!

  • >