Power BI Measure Sum and Subtract Example

In this Power BI Tutorial, We will discuss how to calculate the sum of the total value in the Power BI table using Power BI Measure. Also, We will see how to subtract any two values from the table by using Power BI Measure or Dax rule.

By taking one simple requirement, I will explain to you how you can use SUM (To add the two different values) function and how to Subtract the two different values using Power BI DAX.

Apart from this, we will see various other examples on Power BI Measure SUM and Power BI Measure Substarct like below:

Power bi measure sum and subtract

In this example, I have an Income Tax Rates table in my Power BI Desktop. This table has many different data types of columns.

It has a Net Wage Earnings After Tax column whose data type is Currency. Similarly, It has another column named as Net Wage Bonus whose data type is also a Currency.

You can see my Income Tax Rates List in the below screenshot:

Here, I want to calculate the total value of two-column as Net Wage Earnings After Tax and Net Wage Bonus. So I need to use the SUM Function in Power BI Measure.

Similarly, I want the value of Net Wage Earnings without Bonus. For that purpose, I need to subtract two column values as Net Wage Earnings After Tax and Net Wage Bonus.

Procedure:

To add and subtract two different values using Power BI Measure, You need to follow the below things as:

• Format the data type of Net Wage Earnings After Tax as the Whole Number
• Format the data type of Net Wage Bonus as the Whole Number
• Apply the SUM formula to add the two different column values using Power BI Measure
• Apply the Subtraction formula to subtract the two different column values using Power BI Measure
• Test the Power BI Measure (SUM and Subtraction formula) by taking a Power BI Table from the Visualization

SUM and Subtract using Power BI Measure

Step-1:

First of all, Open your Power BI Desktop and Sign in with your Microsoft account. Get the SharePoint List from the SharePoint Online Site to your Power BI Desktop.

If you want to know how to get the SharePoint List from SharePoint Online Site to Power BI Desktop, then you can refer to this link: Get SharePoint List to Power BI Desktop

You can see my Income Tax Rates tables in Power BI Desktop as shown below:

In the above screenshot, Under the Fields section, you can see my table (Income Tax Rates) with a down arrow symbol. Once you will expand that symbol, then you can see all the columns that are available in the table.

Step-2: (Format the data type as the Whole Number of Net Wage Earnings After Tax)

Now you have to format the data type of Net Wage Earnings After Tax as the Whole Number. By default, the data type was present as Text.

To change the data type of [Net Wage Earnings After Tax], Go to the Income Tax Rates table under the Fields section and click on the [Net Wage Earnings After Tax] column. Now go to the Modelling tab and select the data type as the Whole Number from the Data type section.

If you want to know more details about the various Power BI Data types, then click on this link: Power BI Data type

Step-3: (Format the data type as the Whole Number of Net Wage Bonus)

In the same way, you need to format the data type of Net Wage Bonus as the Whole Number. By default, the data type was present as Text.

To change the data type of [Net Wage Bonus], Go to the Income Tax Rates table under the Fields section and click on the [Net Wage Bonus] column. Now go to the Modelling tab and select the data type as the Whole Number from the Data type section.

After formatting both the columns of the table, those columns will look like the below screenshot:

Power BI Measure Sum

Apply the SUM formula to add the two different column values using Power BI Measure:

Now, You have to apply the rule to add the two different column values. For this purpose, create a New measure in the Income Tax Rates table.

Once you have created the new measure, apply the below formula to add the values of two different columns:

Total Net Wage Earnings = SUM('Income Tax Rates'[Net Wage Earnings After Tax])+SUM('Income Tax Rates'[NetWageBonus])

Also, you can refer to the measure formula from the below screenshot:

Power BI Measure Subtract

Apply the Subtraction formula to subtract the different column values using Power BI Measure:

Similarly, As like the Power BI SUM, do the same process for Subtraction also. Create another new Measure and put the below formula:

Net Wage Earnings without Bonus = Calculate (Sum('Income Tax Rates'[Net Wage Earnings After Tax]))-Calculate (Sum('Income Tax Rates'[NetWageBonus]))

You can refer the Subtraction measure formula from the below screenshot:

Step-6: (Test the Power BI Measure (SUM and Subtraction formula) by taking a Table from the Visualization)

After completing all the steps, Don’t forget to test your requirements. For testing purposes, Click on the Table from the Visualizations section.

Just drag and drop both the Measures(Total Net Wage Earnings and Net Wage Earnings without Bonus) to Values section of the Table.

In this example, I have used the below columns to show the values in a tabular format. Those columns are:

• Country
• Net Wage Earnings After Tax
• Income Tax Rate
• Employee Social Security Contributions
• Net Wage Bonus

After putting all the columns and measures in the Table, then the table visual is looking like the below screenshot:

Now, let us see a few examples of Power BI Measure SUM.

To implement all the above topics, here we are going to use a sample excel report. Not only excel report, but we can also use SharePoint list, Text file, SQL data, CSV file, etc to create a report on Power BI.

Power BI Measure SUM column

• In power BI, we used SUM to evaluate the totals(adds) of all the numbers in a column. We can calculate the SUM only on a numerical column in Power BI.
• By creating a Measure we can use the SUM with a DAX expression:

Measure = SUM(<Column Name>)

• For example, we will take the Product Sub-category and Sales data from that sample report to calculate the total sales.

• On the Home/Modeling tab > New Measure. It will create a new measure under the model data.

• Insert the below DAX expression on the Formula bar to create a Mesure.

Total Sales = SUM(Orders[Sales])

This is how we can calculate SUM using a Power BI Measure.

Power BI Measure SUM multiple columns

Now we will see how the SUM functions work with multiple columns using DAX expression on Power BI.

• Similarly, here we will take the Product Sub-category, its Profit, and Sales details.

• Then we will calculate the SUM of multiple columns.

Net Sale = SUM(Orders[Sales]) + SUM(Orders[Profit])

• We can see the total net sales on this above table using Power BI Measure. This is how we can use SUM to calculate multiple columns in Power BI.

Power BI Measure SUM with filter

A filter function returns a table that has been filtered. Now we will see how to calculate the SUM of filtered parts from a whole data.

• For example, according to our sample data we have a whole data of the Product Sub-category and it’s Profit report.

• Let’s create a Measure that will filter and calculate the SUM of a specific item (ex- Computer Peripherals). To execute this the DAX expression is:

SUM_of_filter_data = CALCULATE(SUM(Orders[Profit]), FILTER(ALL(Orders[Product Sub-Category]), Orders[Product Sub-Category]="Computer Peripherals"))

• The above table is showing only those particular data and it’s SUM. On this way we can FILTER the data and calculate it’s SUM using Power BI Measure.

Power BI Measure SUM IF

In Power BI, the IF function checks whether the condition is met and returns a TRUE value otherwise it returns a FALSE value.

• For example, we are going to use the data of the Product Sub-category and it’s Sales report.

• We will calculate if the sales report is greater than 15000 then it will return TRUE value (i.e. UP) otherwise it will return FALSE value(i.e. DOWN).
• To execute this, here we will create a Measure and the formula is:

Measure_IF = IF(SUM('Orders'[Sales]) > 15000, "UP", "Down") 

• The above table got filtered and visualized the measure whether it is UP and Down, according to our IF condition. This is how we can calculate the SUM in Power BI Measure by using IF.

Power BI Measure SUM by group

• By default, Power BI shows the data as a group by calculating its SUM. But, we will see how to calculate SUM by grouping the data through a DAX expression on Power BI Desktop.
• For this, we have created a SharePoint list and import that data to Power BI.

Approach-1:

1. On Power BI Report page, create a table chart to show the all Account details.

2. Now, we will create a measure that will calculate the SUM and create a group for all the user’s Account.

Measure_SUM_by_Group = CALCULATE(SUM('Account Table'[Saving Amount]),FILTER(ALLSELECTED('Account Table'),'Account Table'[Users Account] = MAX('Account Table'[Users Account])))

Approach-2:

There is another method to evaluate the SUM by group data using Power BI Measure. For this, We have to create another measure under the Account table.

SUM_by_Group = CALCULATE(SUM('Account Table'[Saving Amount]),ALLEXCEPT('Account Table','Account Table'[Users Account]))

These are the way to calculate Calculate SUM by group using Measure on Power BI.

Power BI Measure SUM by date

• In Power BI, it allows us to calculate the total day between two different dates. In our sample data, we have two date columns as Order Date and Ship Date.

• Now we will create a Measure that will calculate the total days in-between Order Date and Ship Date.

Total_Days = SUMX('Orders', DATEDIFF('Orders'[Order Date].[Day], 'Orders'[Ship Date].[Day],DAY))

• We can see the difference between Order Date to Ship Dates is calculated and returns a numerical value.

This is how to calculate Dates using Power BI Measure SUM.

Power BI Measure SUM if contains

Now we will see how a Power BI Measure works with Contains() function and calculate its SUM.

• Let’s create a table chart using sample data, containing these columns such as Product Container, its Sub-Category, and Sales.

• Using the above table, We will create a Measure to calculate SUM of sales, which contains only “Jumbo Box” and “Tables“.

SUM_IF_CONTAINS = CALCULATE (

    SUM ( Orders[Sales] ),

    FILTER (

        'Orders',

        CONTAINS ( 'Orders', 'Orders'[Product Container], "Jumbo Box" )

            && CONTAINS ( 'Orders', 'Orders'[Product Sub-Category], "Tables" )

    )

)

• The above table is only showing the data and it’s SUM which contains Jumbo Box and Tables according to our expression.

In this way, we can calculate the SUM using IF Contains() functions.

Power BI Measure SUM of multiple Measure

In Power BI, a Measure allows calculating the SUM of multiple Measures.

• Let’s take an example of our sample data and create a table using columns such as Product Sub–Category, it’s Profit, and Sales.

• Now we will create measures for both Sales and Profit columns. To calculate total sales, the DAX expression is:

Total Sales = SUM(Orders[Sales])

• For total Profit, the expression is:

Total Profit = SUM(Orders[Profit])

• When we will use these measures in our table, the table will visualize having sum of Total Sales and Total Profit:

• Similarly, We will create another Measure which calculate the SUM of net sales of Products by using Multiple measures on Power BI.

Net_Sales = [Total Sales] + [Total Profit]

• We can see after applying the Measure, it returns the SUM of Total Sales measure and Total Profit measure.

This is how we can calculate a SUM using multiple Measures on Power BI.

Power BI Measure SUM with condition

Here, We will see how to calculate a SUM using a Condition on Power BI Measure.

• Using the sample data, let’s create a table having column of Product Sub-Category and it’s Shipping Cost.

• Now we will create a Measure that calculates the SUM according to our condition.

Sum_with_Condition = CALCULATE(SUM('Orders'[Shipping Cost]), 'Orders'[Product Sub-Category]="Paper") 

• This will evaluate the SUM of shipping cost only for “Paper”.

This is how to evaluate the SUM of a particular column value by using conditions in Power BI.

Now, let us see a few more examples on Power BI Measure Subtract.

To implement all the below examples, we are going to use this sample data. If you have no idea how to import excel data to Power BI, then check to Create a Power BI report from Excel using Power BI Desktop.

Power BI Measure subtract two columns

Now we will see how to subtract two columns using Measure in Power BI.

• For example, From that sample data, We will take two numeric fields (Profit and Sales column) to perform this.

• To calculate the difference or subtracts value between two-column, We have to create a measure on Power BI.

Profit_Sale_substract_value = SUM(Orders[Sales]) - SUM(Orders[Profit])

To visualize the value here we used a table chart. This is how to do subtract two columns using Power BI Measures.

Power BI Measure Subtract Dates

Here we will see how to subtract two date columns using a Power BI Measure.

• To perform this, We have to need two date columns. Luckily, We have date columns in that sample data (i.e. Order Date & Ship Date).

• Now we will format these long date data types into short date data types. For this: go to Data view > click on the date column > select sort date from the format option.

• Now both the date columns will appear like this:

• Now we will create a measure to calculate the difference between two date columns using Power BI Measure.

No_of_Days = SUMX('Orders', DATEDIFF('Orders'[Order Date], 'Orders'[Ship Date], DAY))

• Without formatting, It will perform the same calculation. But for our better understanding, we formatted the date column.

This is how to Subtract two dates using Power BI Measure.

Power BI Measure Substract two Measures

Now we will see how to calculate subtraction of two Measures using a Power BI Measure.

• By using this sample data, we will calculate the Cost Price of the product. The formula is:

Cost Price = Selling Price - Profit

Step-1:

First, we will create two measures to calculate the Total Sale and Total Profit.

Total Selling Price = SUM(Orders[Sales])

Total Profit = SUM(Orders[Profit])

Step-2:

Then we will create another measure to calculate the cost price of the product.

Cost Price = [Total Selling Price]-[Total Profit]

Step-3:

Now we will visualize all the measures and it’s calculation through a table chart like this:

This is how we can evaluate the subtraction of two measures using another measure on the Power BI Desktop.

Power BI Measure Subtract two columns from different tables

Here we will see how to calculate the subtraction of two columns (from different tables) using Power BI Measure.

Step-1:

For example, we will create two tables based on the product’s order and delivery. To create a table on Power BI Desktop, go to home > Enter data.

Table-1: Product’s Order

Table-2: Product’s Delivery

Step-2:

• Now we will create a measure under the Product’s order table to calculate the total product’s sales from the orders quantity of the current month.
• Insert the below DAX expression on the formula bar to create a measure.

OrderToSale = 
VAR NetValue = CALCULATE(SUM('Product''s Order'[Order Quantity]),MONTH('Product''s Order'[Order Date])=MONTH(TODAY()))
VAR yyyy = MAXX('Product''s Order','Product''s Order'[Order Date].[Year])
VAR NetSales = CALCULATE(SUM('Product''s delivery'[Deliver Quantity]),FILTER(ALL('Product''s delivery'),YEAR('Product''s delivery'[Delivery Date])=yyyy&&MONTH('Product''s delivery'[Delivery Date])=MONTH(TODAY())))
RETURN NetValue-NetSales

• In the above screenshot, as the current month is August and the total order quantity is 39(25+14), the total delivers quantity is 25(17+8). So the rest of the order to sell is 14(39-25).
• For more clarification, we filter the data according to the current month in both tables. Then it looks like:

This is how to subtract two columns from different tables in Power BI using a measure.

Power BI subtracts two columns

• In Power BI, there is no direct Subtract function to subtract the two columns. So if we want to remove two columns then we have to sum the two column values and then we can subtract them using the (-) icon.
• For example, if we want to calculate the Profit value and the formula is ( Profit = SP – CP) So initially, we have to sum the SP value, and then subtract it with the sum of the CP value.
• Here is an excel file that I have used in this example, you can download it from here.

How to subtract two columns in Power BI

Let us see how we can subtract two columns in Power BI.

• In this example, we will use the Product Table data, We will take two number data type columns ( SP and CP) and calculate the Profit Value.
• The below sample data is used in this example,

• Make sure the data has been loaded into the Power BI desktop, Click on the new measure and apply the below formula.
• To calculate the difference or subtracts the value between two-column, We have to create a measure on Power BI.

Profit = SUM('Product'[SP])-SUM('Product'[CP])

Where,

1. Profit = Measure name
2. Product = Table name
3. SP and CP = column names

• To visualize the value I have used a table visualization chart below:

This is how to subtract two columns in Power BI.

Also Read: How to remove rows in power query editor [With various examples]

Power BI subtracts two columns using a measure

Let us see how we can subtract two columns using a measure or DAX in Power BI.

In this example, we will use the Sales Table data, We will take two number data type columns ( Sales and Profit) and calculate the Gain Value.
The below sample data is used in this example,

• Make sure the data has been loaded into the Power BI desktop, To calculate the difference or subtract the value between two-column, We have to create a measure on Power BI and Click on the new measure.
• Make sure the column types are in the number data type format.

Gross profit = SUM(Sales[Sales])-SUM(Sales[Profit])

Where,

1. Gross profit = Measure name
2. Sales = Table name
3. Sales and Profit = column names

• To visualize the value here I have used a table visualization chart below, in the columns section just drag and drop the values that you want to display.

This is how to subtract two columns using a measure or DAX in Power BI.

Check out: Power BI filter between two dates [With 15+ Examples]

Power BI subtracts two columns from different tables

Let us see how we can subtract two columns from different tables in Power BI,

• Here I am going to use the below-mentioned two tables, one is the Product table, and another table is the sales table.
• In this example, we will calculate the gross profit value, and the formula to calculate the Gross profit value is ( Sales – COGS).
• Make sure one table is common in both tables so that we can make the relationship between the two tables.

Table 1: (Product Table)

Table 2: (Sales Table)

Step:1

• Make sure the data has been loaded into the Power BI desktop.
• Choose the Model and select the common country field and manage the relationship between the Sales column and the Product column.
• Here I have made the relationship with the country column field.

Step:2

• Click on the new measure and apply the below formula. To calculate the difference or subtracts the value between two-column from two different tables.
• Here Select the Sales column field from the sales table and the COGS column field from the product field. Make sure the two-column fields should be the whole number date type.

Gross Profit = SUM(Sales[Sales]) - SUM('Product'[COGS])

where,

1. Gross Profit = measure name
2. Sales and Product = table names
3. Sales and COGS = column names

Step 3:

• Now select the table visual and drag and drop the column fields from different tables as shown below:
• In the below screenshot, you can see that table visually calculates the Gross profit by subtracting the Sales value from the COGS value.

This is how to subtract two columns from different tables in Power BI.

Read: Power BI Sum Multiple columns

Power BI subtracts two calculated columns

Let us see how we can subtract two calculated columns in the Power BI,

• In this example, initially, we will calculate the gain value and loss value based on the SP and CP. After that, we will see how to subtract the two calculated columns.
• In this example, I have used the below-mentioned sample data.

• Once the data is loaded into the Power BI desktop, select the New column option to calculate the gain and loss value based on the SP and CP values.
• Under the Table tools select the New column and apply the below-mentioned formula:

Gain = 'Product'[SP]-'Product'[CP]

Loss = 'Product'[CP]-'Product'[SP]

Where,

1. Gain, Loss = Measure Name
2. Product = Table name
3. SP and CP = column names

• Once the calculated column is created, then click on the new measure and apply the below-mentioned formula:

calculated value = SUM('Product'[Gain]) - SUM('Product'[Loss])

Where,

1. Calculated Value = Measure Name
2. Product = Table name
3. Gain and Loss = column names

• Now select the Matrix visual and drag and drop the calculated column fields as shown below:
• In the below screenshot, you can see that Matrix visual calculate the Gross profit by subtracting the Sales value from the COGS value.

This is how to subtract two calculated columns in Power BI.

Also Read: Power BI divides two columns [With Examples]

Power BI subtracts multiple columns

Let us see how we can subtract multiple columns in Power BI,

• yes, it is possible to subtract more than two columns in Power BI. Initially it subtracts two values, with the result subtracting the other value.
• For example, if we 3 columns a,b,c where the values are a= 1000; b=900; c= -100
• It calculates (a-b [1000 -900]) and the result is 100). Then it calculates 100 -(-100) and the result is 200. This is how it calculates the multiple columns in Power BI.

In this example, I have used the same Product sample table to subtract two or more columns in Power BI.

• Load the data into the Power Bi desktop, then click on the new measure and apply the below-mentioned formula:

Subtraction = 'Product'[SP]-'Product'[CP]-'Product'[Loss]

Where

1. Subtraction = Measure name
2. Product = Table name
3. SP, CP, Loss = Column names

• In the below screenshot, you can see that the subtraction column calculates and displays the Multiple columns.

This is how to subtract multiple columns in Power BI.

Check out: Stacked Bar Chart in Power BI [With 27 Real Examples]

Power Query subtracts two columns

Let us see how we can subtract two columns using Power Query in Power BI,

In this example, I have used the same Product sample table to subtract the two columns using the Power Query editor in Power BI.

• Here, we will find the Loss value and the formula is ( Loss = CP -SP)
• Initially Load the data using the get data option and click on the Home and Select the Transform data option from the ribbon as shown below:

• Now, In the Power Query editor, Select the Table and click on the add column option.
• After that, select the column that you want to Subtract from other columns Expand the standard option and select the Subtract operation that you want to perform. In my case, it is the CP column
• Here I am going to select Subtract operation as mentioned below:

• Under the values, select the columns that you want to Subtract with in my case it is the SP column, and click on the Ok button.

• Now, you can see the Subtraction column displays the result of CP-SP to find the loss value columns using Power Query in Power BI.
• Click on the Close and Apply option from the ribbon, so that the changes will be saved.

This is how to subtract two columns using Power Query in Power BI.

Check: Power BI Bookmarks [With 21 Examples]

Power BI subtracts two columns in a matrix and table visual

Let us see how we can subtract two columns and display the values in the matrix visual as well as the table visual in Power BI.

• In this example, I am going to use the below-mentioned project sample data, where I am going to calculate the Pending projects based on the Assigned projects and Completed Projects.

• Make sure the data has been loaded to the Power BI desktop, create a New measure and apply the below formula:

Pending Projects = SUM(Projects[Assigned Projects])-SUM(Projects[Completed Projects])

where,

1. Pending Projects = Measure Name
2. Projects = Table Name
3. Assigned Projects and Completed Projects = Column Name

• Now select the visual and drag and drop the column fields that you want to display.
• The below screenshot represents the values in the Matrix visual Power BI.

• The below screenshot represents the values in the Table visual Power BI.

This is how to subtract two columns and display the values in the matrix visual as well as in the table visual in Power BI.

Read: Clustered Column Chart in Power BI [With 45 Real Examples]

Power BI subtracts two date columns

Let us see how to subtract two date columns using the measure in Power bi.

• In this example, I am going to use the below sample table to calculate the difference between the Start Date and the End date.

• Load the data to the Power BI desktop, create a New measure and apply the below formula:

Date Difference = SUMX('Projects', DATEDIFF('Projects'[Start Date], 'Projects'[End Date], DAY))

where,

1. Date Difference = Measure Name
2. Projects = Table Name
3. Start Date and End Date = Column Name

• Now select the Table visual and drag and drop the column fields that you want to display:
• In the below screenshot, you can see that the Table visual display the date difference between the two date columns.

This is how to subtract two date columns using the measure in Power BI.

Check: Power bi change color based on value [With 13 real examples]

Power Bi calculates the difference between two measures

Let us see how to calculate the difference between values of two Measures using a Power BI Measure.

Here I have used the below-mentioned sample data:

• Load the data and create two measures to calculate the gain and loss value. create two new measures and apply the below-mentioned formula to calculate the gain and loss value:

Gain = SUM('Product'[SP]) - SUM('Product'[CP])

Loss = SUM('Product'[CP])- SUM('Product'[SP])

Where,

1. Gain, Loss = Measure Name
2. Product = Table name
3. SP and CP = column names

• After that create a new measure and subtract the measure values as mentioned below:

Measure = 'Product'[Gain] -'Product'[Loss]

Where,

1. Gain, Loss = already created Measures Name
2. Measure = Newly created Measure Name

• Then select the table visual chart and display the value in it as below:

This is how to calculate the difference between values of two Measures using a Power BI Measure in Power BI Desktop.

Read: Power BI calculated column [With 71 Useful Examples]

Power BI percentage difference between two columns

Let us see how we can find the difference percentage between two columns in Power BI,

• In this example also I am going to use the same product table, which I am using for the previous heading.

• Load the data into the Power BI, and then first find the difference between the two columns, and then based on the difference value we can calculate the percentage value.
• Create a measure and apply the below formula:

Profit = SUM('Product'[SP])-SUM('Product'[CP])

Where,

1. Profit = Measure Name
2. Product = Table Name
3. SP and CP = Column Names

• After that to find the profit percentage, the formula is profit = profit*100/CP.
• Create a new measure and apply the below formula:

Profit Percentage = 'Product'[Profit]*100/SUM('Product'[CP])

Where,

1. Profit Percentage = Measure Name
2. Product = Table Name
3. CP = Column Names

• Before that for the Profit Percentage measure change the data type from general to Percentage.

• In the below screenshot, you can see the calculated percentage value in the table visual.

This is how we can find the difference percentage between two columns in Power BI.

Also, check: Power BI sync slicers [With 15 useful examples]

Power BI average difference between two columns

Let us see how to subtract two date columns and find the average using the measure in Power Bi.

• In this example, I am going to use the Projects sample table to calculate the difference between the Start Date and the End date, and then based on the difference value we can calculate the average value.

• Load the data to the Power BI desktop, create a New measure and apply the below formula:

Date Difference = SUMX('Projects', DATEDIFF('Projects'[Start Date], 'Projects'[End Date], DAY))

where,

1. Date Difference = Measure Name
2. Projects = Table Name
3. Start Date and End Date = Column Name

• Now, create another measure to find the average of the date difference value, for that create a New Measure and apply the below formula:

AVG OF DAY = AVERAGEX('Projects',Projects[Date Difference])

• Now select the Table visual and drag and drop the column fields that you want to display:
• In the below screenshot, you can see that the Table visual display the date difference between the two date columns.

This is how to subtract two date columns and find the average using the measure in Power BI.

Read: Power BI Group By [With 51 real examples]

Power Bi subtracts two columns based on condition

Let us see how to subtract two columns based on the condition using the measure in Power Bi.

Here I am going to use the Sales table data as mentioned below:

• Here, first, we will calculate the total sales by using the sum function measure.
• Then we will find how many sales order has been completed and later we will subtract the total sales from the completed orders to find the result of the remaining sales orders.
• Load the data to the desktop and click on the new measure from the ribbon in power bi and apply the below measure:

Total Sales = SUM(Sales[Sales])

• Now we will find the completed Sales orders, Click on the new measure in the Sales table and apply the below measure:

Total profit = SUMX(VALUES(Sales[Country]),CALCULATE(SUM(Sales[Completed Orders])))

• We will find the remaining sales order value, select the new measure and apply the below measure based on the Condition it displays the results and we can display them.

Remaining orders = 
IF(ISBLANK([Total profit]),BLANK(),Sales[Total Sales] - [Total profit])

• Now create a table visual from the visualization pane, then drag and drop columns that you want to display in the chart.
• In the below screenshot you can see the Power bi measure subtract two measure based on the condition.

This is how to subtract two columns based on the condition using the measure in Power BI.

Power BI subtracts two columns Per monthly basis

Let us see how we can subtract two columns from different tables in a monthly basis using DAX in Power BI,

• Here I am going to use the below-mentioned two tables, one is the Orderstable, and another table is the sales table.
• In this example, we will calculate the Remaining Orders value based on the Sales occurred.
• Make sure one column is common in both tables so that we can make the relationship between the two tables. In this example the Month column is common.

Table 1: (Orders Table)

Table 2: (Sales Table)

Step:1

• Make sure the data has been loaded into the Power BI desktop.
• Choose the Model and select the common country field and manage the relationship between the Sales Table Month column and the Orders Table Month column.
• Here I have made the relationship with the Month column field.

Step:2

• Click on the new measure and apply the below formula. To calculate the difference or subtracts the value between two-column from two different tables.
• Here Select the Sales value column field from the sales table and the Orders value column field from the Orders Table. Make sure the two-column fields should be the whole number data type.

Balance Orders = CALCULATE(SUM(Orders[Orders]) - SUM(Sales[Sales]))

where,

1. Balance Orders = measure name
2. Sales and Orders = table names
3. Sales and Orders = column names

Step 3:

• Now select the table visual and drag and drop the column fields from different tables as shown below:
• In the below screenshot, you can see that table visually calculates the Balance Orders  by subtracting the Sales value column field from the sales table and the Orders value column field from the Orders Table.

• You can also use filter on this visual, to filter the data based on the Month by expanding the filters pane as mentioned below:

This is how to subtract two columns from different tables in a monthly basis using DAX in Power BI.

Also, You may like the following Power BI tutorials:

• DAX Filter function (Text column) in Power BI
• Get Current Month Sales Report using Power BI Measure
• Get Next Month Sales Report in Power BI
• Power BI Error: This content isn’t available
• Data Labels in Power BI
• Power BI Buttons
• Power BI Query Group Tutorial
• Export Power BI Reports to Excel
• Subtraction in Power bi using DAX
• How to change data source in Power Bi
• How To Use Azure Text Analytics In Power BI

In this Power BI Tutorial, we learned how to calculate the sum of the total value in the Power BI table using Power BI Measure and also, how to subtract any two values from the table by using Power BI Measure or Dax rule.

Also, we saw the below examples on Power BI SUM and Subtract.

• Power BI Measure Sum
• Power BI Measure Subtract
• Power BI Measure SUM column
• Power BI Measure SUM multiple columns
• Power BI Measure SUM with filter
• Power BI Measure SUM IF
• Power BI Measure SUM by group
• Power BI Measure SUM by date
• Power BI Measure SUM if contains
• Power BI Measure SUM of multiple Measure
• Power BI Measure SUM with condition
• Power BI Measure subtract two columns
• Power BI Measure Subtract Dates
• Power BI Measure Substract two Measures
• Power BI Measure Subtract two columns from different tables
• Power BI subtracts two columns
• How to subtract two columns in Power BI
• Power BI subtracts two columns using a measure
• Power BI subtracts two columns from different tables
• Power BI subtracts two calculated columns
• Power BI subtracts multiple columns
• Power Query subtracts two columns
• Power BI subtracts two columns in a matrix and table visual
• Power BI subtracts two date columns
• Power Bi calculates the difference between two measures
• Power BI percentage difference between two columns
• Power BI average difference between two columns
• Power BI subtracts two columns based on the condition
• Power BI subtracts two columns Per monthly basis