cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
andysross Frequent Visitor
Frequent Visitor

Calculate difference between two rows

speed        difference

 

52               0

63               11

76               13               

61               5

 

How do I create a difference column in power bi like the simple calculation in excel =A1-A2 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate difference between two rows

Yes, measures are often the best solution, but there is a way to do it as a column as well: http://excel-inside.pro/blog/2015/11/05/absolute-and-relative-references-in-power-query/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




6 REPLIES 6
Super User
Super User

Re: Calculate difference between two rows

You could this by creating measures, which you can then reference.

 

As per your example the following:

 

Speed Total = sum('TableName'[Speed])

DifferenceTotal = sum('TableName'[Difference])

Difference = [Speed Total] - [Difference Total]

The final measure would then show you the difference. If you wanted to see it by other columns, you would then just drag them into your table.


Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"
andysross Frequent Visitor
Frequent Visitor

Re: Calculate difference between two rows

I am not sure if I am reading your solution correctly.

 

I get this error: A circular dependency was detected: TableName[Difference].

 

So Difference is a new column with the result of row1 - row2 in the speed coulumn and so on.

 

Thank you.

Super User
Super User

Re: Calculate difference between two rows

Hi @andysross

 

Please ensure that you create them as Calculated Measures and NOT calculated columns. That is why you are getting the error as described below.


Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"
Super User
Super User

Re: Calculate difference between two rows

Yes, measures are often the best solution, but there is a way to do it as a column as well: http://excel-inside.pro/blog/2015/11/05/absolute-and-relative-references-in-power-query/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




andysross Frequent Visitor
Frequent Visitor

Re: Calculate difference between two rows

I ended up using this method. Thankyou

 

Difference = var A = CALCULATE( MAX('table'[id]))-1 return If('table'[id]=0,'table'[speed], 'table'[speed] - CALCULATE(SUM('table'[speed]),FILTER('table','table'[id] = A)))

sirgseymour Regular Visitor
Regular Visitor

Re: Calculate difference between two rows

So I am trying to do a similar thing. I have a flat SQL table with insurance premium values by year, region program and contract and want to be able to compare the premiums year on year aggregated by region and program... Any ideas anyone?

 

Thanks