cancel
Showing results for
Did you mean:
andysross 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

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/

Proud to be a Datanaut!

Imke Feldmann

6 REPLIES 6 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.

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

Re: Calculate difference between two rows

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

Re: Calculate difference between two rows

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

"Proud to be a Datanaut!" 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/

Proud to be a Datanaut!

Imke Feldmann

andysross 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

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