Subtraction in Power bi using DAX

SharePoint Online training courses

In this Power BI Tutorial, We will discuss how to subtract two column values from different tables per monthly basis using Power BI DAX rule.

Also, We will see how to subtract total Sales values from different tables using the Power BI Measure.

By taking one simple requirement, I will explain to you that how you can subtract values from two different tables per monthly basis and as well as without a monthly basis.

Requirement:

First of all, I have two tables in my Power BI Desktop. Those two table names are:

  1. Sales Orders
  2. Sales

In the Sales Orders list, It has below columns:

  • Title (By default column)
  • Order Date (Date/Time Data Type)
  • Total Order (Number Data Type)

Similarly, In the Sales list, It has below columns:

  • Title (By default column)
  • Bill Date (Date/Time Data Type)
  • Product Measure (Choice Data Type)
  • Total Product (Number Data Type)

You can see both the Sales Orders and Sales Lists in the below screenshot.

subtraction in power bi

In the above screenshot, Both of the Date column (Order Date and Bill Date) are having many items with different dates, months and years with MM/DD/YYYY format.

Now what I have to do is, As per my requirement, I have Total Order (from Sales Orders list) and Total Product (from Sales list). Now I have to calculate the total sales from the total orders of the current month.

That means I have to display the products which are expecting to sale from the total orders in the current month.

Power BI Subtract values from different tables

To get the total sales of products from total orders of the current month, You have to do these below things as:

  • Format the data type of Order Date and Bill Date as Date/Time
  • Format the data type of Total Order and Total Product as the Whole Number
  • Apply the subtract formula for the current month using Power BI Measure
  • Test the Power BI Measure by taking a Card from the Visualization

Subtract Two Columns From Different Tables Per Monthly Basis Using Power BI DAX

Step-1:

First of all, Open your Power BI Desktop and Sign in with your Microsoft account. Get the SharePoint List from 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 this link: Get SharePoint List to Power BI Desktop

You can see my Sales Orders and Sales tables in Power BI Desktop as shown below:

Subtraction in power bi using DAX

In this above screenshot, Under the Fields section, you can see both the tables by using the down arrow symbol. Once you will expand that symbol, then you can see all the columns that are available in the table.

For screenshot purposes, Here, I have expanded only one table i.e. Sales Orders.

Step-2: (Format the data type as Date/Time of Order Date and Bill Date)

Now you have to format the data type of [OrderDate] and [BillDate] columns as the Date/Time.

To change the data type of [Order Date], Go to the Sales Orders table under the Fields section and click on the [Order Date] column. Now go to the Modelling tab and select the data type as Date/Time from the Data type section.

Similarly, To change the data type of [Bill Date], click on the [BillDate] column from the Sales table and go to the Modelling tab and select the data type as Date/Time from the Data type section.

If you want to know the 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 Total Order and Total Product)

Now you have to format the data type of [TotalOrder] and [TotalProduct] columns as the Whole Number.

To change the data type of [TotalOrder], Go to the Sales Orders table under the Fields section and click on the [TotalOrder] column. Now go to the Modelling tab and select the data type as the Whole Number from the Data type section.

Similarly, To change the data type of [TotalProduct], click on the [TotalProduct] column from the Sales table and 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 different tables, the columns will look like the below screenshot:

power bi subtract columns from different tables

In the Sales table also, the [BillDate] and [TotalProduct] columns will look like as the above screenshot.

Step-4: (Apply the subtract formula for the current month using Power BI Measure)

At last, You have to apply the rule to get the total sales of products from total orders of the current month. For this purpose, create a New measure in the Sales Orders table.

Once you have created the new measure, apply the below formula to get the current month:

Order To Sale = 
VAR NetValue = CALCULATE(SUM('Sales Orders'[TotalOrder]),MONTH('Sales Orders'[OrderDate])=MONTH(TODAY()))
VAR yyyy = MAXX('Sales Orders','Sales Orders'[OrderDate].[Year])
VAR NetSales = CALCULATE(SUM(Sales[TotalProduct]),FILTER(ALL(Sales),YEAR(Sales[BillDate])=yyyy&&MONTH(Sales[BillDate])=MONTH(TODAY())))
RETURN NetValue-NetSales

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

how to subtract two columns from different tables in power bi per monthy basis

Step-6: (Test the measure)

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

Just drag and drop the Measure(Order To Sale) to Fields section of the card. When you put the measure into the fields section, then you can see your total product values which are sold out of the current month as shown below:

power bi subtract values from two tables

Here, I have taken both the tables (Sales Orders and Sales) for reference so that you can understand easily.

As my current month is November. Hence, in the Sales Orders list, it is having a total of 90 orders whereas it is only sold out 40 numbers of products in the Sales list.

So it is calculating the rest products that is expecting to sale in this current month is 50 only. (i.e. 90-40=50)

Subtract Total Sales from Different Tables

Similarly, If you want to subtract the total sales from the Sales Orders list (not for the current month, it’s for all month), then create a New measure and put this below formula as:

Measure = SUM('Sales Orders'[TotalOrder]) - SUM(Sales[TotalProduct])

Now take another Card from the Visualization and drag the Measure to Fields section of the card.

When you put the measure into the fields section, then you can see your total product values as shown below:

power bi subtract two tables

Here also, I have shown you both tables (Sales Orders and Sales) for reference so that you can understand easily.

In the above screenshot, You can see the Sales Orders list is having a total of 200 orders whereas, in the Sales list, it is a total of 89 numbers of products that are sold out.

So it is subtracting the total number of Sales Products are 111 only. (i.e. 200-89=111).

Once all things will over, you can Save and Publish your Report in your Desktop. After that, when you will sign in with your Power BI Online service, you can see that report in your Reports section.

In case, If you want to change your data or add some data in your lists, then it will affect when you will refresh the data in your Power BI Desktop. Similarly, You have to refresh the report in the Power BI Online Services also.

Also, You may like following Power BI tutorials:

Hence in this Power BI Tutorial, We discussed how to subtract two column values from different tables per monthly basis using Power BI Dax.

Also, We saw how to subtract total Sales values from different tables using the Power BI Measure by describing the above requirement.

    • >