In this Power bi tutorial, we will learn about power bi measure subtract with example. And also we will discuss the below points:
- Power bi measure subtract two columns
- Power bi measure subtract dates
- Power bi measure subtract two columns with different tables
- Power bi measure subtract two measure
- Power bi measure today minus date
- power bi quick measure subtraction
- Power bi measure date minus 1 day
- Power bi measure substract negative value
Power bi measure subtract
In power bi, to subtract to two values we will use the subtract operator ‘-‘.
The syntax of subtraction is
Measure = value 1 - value 2
Power bi measure subtract two columns
Here we will see how to subtract two columns by using the measure in power bi. We will use the below sample table, to subtract two columns.
Here we will calculate the difference between the amount 1 and the amount 2 columns. To calculate the difference we will create a new measure in Power BI.
- For this click on the New measure from the ribbon in power bi desktop.
- Then write the below Dax formula:
Diff = SUM('Table'[Amount 2]) - SUM('Table'[Amount 1])
- Now, select the table visual from the visualization pane. Then drag and drop the Order column, Amount1 column, Amount 2 column, and diff measure.
- In the below screenshot you can see the difference between two columns in power bi desktop.
Read Power bi sum group by multiple columns
Power bi measure subtract dates
Here we will see how to subtract dates using the measure in power bi. We will use the below sample table to calculate the difference between the two dates.
Here we will subtract the start date and end date by using the measure in power bi desktop.
- For this click on the New measure from the ribbon in power bi.
- Then write the below measure to substract the start date and end date.
NoOfDays = SUMX('Project', DATEDIFF('Project'[Start Date], 'Project'[End Date], DAY))
- Select the table visual from the visualization pane to show the outcome.
- For this in the value field, drag and drop the project name column, start date column, end date column and no.of days measure.
- In the below screenshot you can see difference between in two dates in days.
Power bi measure subtract two columns with different tables
Here we will see how to calculate subtraction between two columns with two different tables in Power BI. We will use the below two tables i.e product Order table and the product ship table.
Product Order
Product Ship
Here we will find the total sales of product from total product of the current month.
- So for this click on the Product Order table. Then click on new measure.
- Then write the Dax formula:
OrderToSale =
VAR NetValue = CALCULATE(SUM('Product Order'[Total Order]),MONTH('Product Order'[Order Date])=MONTH(TODAY()))
VAR yyyy = MAXX('Product Order','Product Order'[Order Date].[Year])
VAR NetSales = CALCULATE(SUM('Product ship'[Total product]),FILTER(ALL('Product ship'),YEAR('Product ship'[Ship Date])=yyyy&&MONTH('Product ship'[Ship Date])=MONTH(TODAY())))
RETURN NetValue-NetSales
- Now lets check the outcome, So select the card visual from the visualization pane.
- Then drag and drop order to sale measure in power bi.
- Then select the table visual from the visualization pane
- In the value field, drag and drop the order date, product name and total order from the product order in the field pane.
- Then create another table visual, and drag and drop the product name, ship date, and Total product from the product ship in the field pane.
Read Power bi date format
Power bi measure subtract two measure
Here we will see how to subtract the two measures in power bi. We have three table student table, course table, and payment table.
- Student table
- Course table
- Payment table
- Here, first, we will calculate the total fee by using the sum function in measure.
- Then we will find how much money paid, then we will substract the total fee- money paid which will show the result how much money remaining to pay.
- So, let’s find the total fee, Select the payment table in the field pane, then click on new measure from the ribbon in power bi.
- Then write the below measure
Fee = SUM(Courses[Fee])
- Now we will find the money paid by the student, Click on the new measure in payment table.
- Then write the below measure:
SumPaid = SUMX(VALUES(Payments[Student ID]),CALCULATE(SUM(Payments[Payment])))
- We will find the remaining fee to be paid by the student and if the fee is paid it will show blank, for this click on the new measure.
- Then write the below measure to calculate:
Amount Remaining =
IF(ISBLANK([SumPaid]),BLANK(),[Fee] - [SumPaid])
- Now create a table visual from the visualization pane, then drag and drop the Student name column (student table), course column and fee column (course table), Sumpaid measure and Amount remaining measure (Payment table).
- In the below screenshot you can see the Power bi measure subtract two measure
Read Power bi group by date range
Power bi measure today minus date
Here we will see how to find difference between date and today by using measures in Power BI. We are using the below sample table to find the difference between from manufacture date to Today.
- To find the difference, we will create a measure, so for this click on the new measure from the ribbon in power bi desktop
- Then write the below measure:
Datedif = SUMX('Manufacture', DATEDIFF(Manufacture[Manufacturing Date], TODAY(), DAY))
Now select the table visual from the visualization pane, then drag and drop the product column, manufacturing date column, and datediff measure from the field pane.
Power bi quick measure subtraction
Here we will see how to subtract two columns by using the quick measure in power bi.
We will use the below sample table to find the diff between amount 2 and amount 1 using the quick measure.
- Load the data using get data in power bi desktop
- Then click on Table, and select Quick measure from the ribbon in power bi desktop
- Now Quick measure window will open, Under calculation, click on the dropdown and select substraction
- Under Base value drag and drop the Amount2 from the field -> Expand table.
- Then under value to substract drag and drop the Amount1. Click on OK.
- Now select the table visual from the visualization pane, then add the order, amount1, amount2 columns and Amount 2 minus Amount 1 measure.
Read How to create a Measure based on Slicer in Power BI
Power bi measure date minus 1 day
Here we will see how to calculate date minus 1 day using the measure in power bi.
For example, if today’s date is 19-08-2021 then will show the result 18-08-2021
So for this, we will create a measure that will show the previous day
- Click on the new measure from the ribbon in power bi desktop.
- Then write the below measure
Minus1days = DATE(YEAR(NOW()),MONTH(NOW()), DAY(NOW()-1))
Now select a table visual, from the visualization pan. Then drag and drop the Minus1dyas measure.
In the below screenshot you can see the power bi measure day minus 1 day.
Power bi measure substarct negative value
Now we will see how to show a negative value using subtract in Power BI Measure.
To implement this here we have created a simple table like this:
We will create a measure that will calculate the subtracts of the two values.
Substarction = CALCULATE(SUM('Table'[value-1])) - SUM('Table'[value-2])
If value1 is greater than value2, then it will return a positive subtract value. Otherwise, it returns a negative subtract value.
This is how to returns a negative value by using Power BI Measure subtracts.
You may like the following Power BI tutorials:
- Power BI Date Function with Examples
- How to create a filter in Power bi
- Power bi show items with no data
- Power bi show value as percentage
- How to create a Power BI Dashboard in Microsoft teams
In this power bi tutorial, we discussed the different types of examples of Power bi-measure subtract. And also we discuss the below points:
- Power bi measure subtract two columns
- Power bi measure subtract dates
- Power bi measure subtract two columns with different tables
- Power bi measure subtract two measure
- Power bi measure today minus date
- Power bi quick measure subtraction
- Power bi measure date minus 1 day
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com