cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andysross
New Member

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

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/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

8 REPLIES 8
edgarjb-68
Frequent Visitor

I have a question on Power BI. How to calculate rows difference on a table? I want to create a colum "started" as below, for each row of this colums I need to calculate like this excel like formula on cell C2 : =IF(AND(A3=A2;B3-B2<1);"No";"Yes") A B C 1 Part Date Started 2 P01 26/03/2019 05:17 yes 3 P01 07/04/2019 18:56 no 4 P01 07/04/2019 19:56 yes 5 P02 26/03/2019 05:17 yes 6 P02 07/04/2019 18:56 no 7 P02 07/04/2019 19:56 yes

Hi @edgarjb-68 ,

please check this method on how to reference a different row in PQ: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po... 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

sirgseymour
Helper I
Helper I

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

GilbertQ
Super User
Super User

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 Super User!







Power BI Blog

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.

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 Super User!







Power BI Blog

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/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!