Forum Discussion
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 ?
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/
8 Replies
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.
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.
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
- 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-power-query-or-power-bi/
