Forum Discussion
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/
Super UserYou 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.
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.
- GilbertQ
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.
- ImkeF
Community Champion
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/
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)))