Power bi measure subtract + 7 useful examples

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.

Power bi measure subtract two columns
Power bi measure 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])
Power bi measure subtract two columns
Power bi measure subtract two columns
  • 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.
Power bi measure subtract two columns
Power bi measure subtract two columns

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.

Power bi measure subtract dates
Power bi measure subtract 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))
Power bi measure subtract dates
Power bi measure subtract dates
  • 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 dates
Power bi measure subtract dates

Read Power bi Date Difference

See also  How to change Power Apps DataCard to dropdown

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

Power bi measure subtract two columns with different tables
Power bi measure subtract two columns with different tables

Product Ship

Power bi measure subtract two columns with different tables
Power bi measure subtract two columns with different tables

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.
Power bi measure subtract two columns with different tables
Power bi measure subtract two columns with different tables

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
Power bi measure subtract two measure
Power bi measure subtracts two measure
  • Course table
Power bi measure subtract two measure
Power bi measure subtracts two measure
  • Payment table
Power bi measure subtract two measure
Power bi measure subtracts two measure
  • 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])
Power bi measure subtract two measure
Power bi measure subtracts two measure
  • 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
Power bi measure subtract two measure
Power bi measure subtracts two measure

Read Power bi group by date range

See also  How to Stop Flow in Power Automate?

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.

Power bi measure today minus date
Power bi measure today minus date
  • 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))
Power bi measure today minus date
Power bi measure today minus date

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 measure today minus date
Power bi measure today minus date

Read Power BI Date Hierarchy

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.

power bi quick measure subtraction
power bi quick measure subtraction
  • 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
power bi quick measure subtraction
power bi quick measure subtraction
  • 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.
power bi quick measure subtraction
power bi quick measure subtraction
  • Now select the table visual from the visualization pane, then add the order, amount1, amount2 columns and Amount 2 minus Amount 1 measure.
power bi quick measure subtraction
power bi quick measure subtraction

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.

See also  Power Apps Checkbox control within Gallery

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 date minus 1 day
Power bi measure date 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:

Power BI measure subtract negative value
Power BI measure subtracts negative value

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.

Power BI measure subtract negative value
Power BI measure subtracts negative value

This is how to returns a negative value by using Power BI Measure subtracts.

You may like the following Power BI tutorials:

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
>