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 that 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 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 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.

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

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 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