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.

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 this 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 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 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 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 these 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:

Also, You may like 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
• [Video Tutorial] Create Power BI Dashboard step by step tutorial
• 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

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.