Forum Discussion
Subtracting Values row wise
Hello everyone,
We are trying, subtract values from two rows present on the same column by identifying the value from a different column.
To understand the scenario better, here's an example
As seen in the above chart, we would like to subtract values from Qtr 2 to Qtr 1 and display the difference in a new column, here the challenge is, all the Qtr are present in a single column and their appropriate values. eg. shown below.
We had already tried PreviousMonth, PreviousYear functionality but ended up with unsatisfactory solutions.
Kindly let us know about your inputs.
Hi azizshaikh,
Based on your description, you want to compare your current row with previous row, right? If that is the case, firstly, use your table according the Amount column by using the Sort Ascending option.
Secondly, add an index column in your current table.
Thirdly, add a new column to compare rows values.
Difference = Table1[Amount]-IF(Table1[Index]=0,Table1[Amount],LOOKUPVALUE(Table1[Amount],Table1[Index],Table1[Index]-1))
For more details, you can review the example in the attached PBIX file.
Reference: http://ignoringthevoices.blogspot.sg/2016/02/comparing-with-previous-row-using.html
Thanks,
Lydia Zhang
1 Reply
Hi azizshaikh,
Based on your description, you want to compare your current row with previous row, right? If that is the case, firstly, use your table according the Amount column by using the Sort Ascending option.
Secondly, add an index column in your current table.
Thirdly, add a new column to compare rows values.
Difference = Table1[Amount]-IF(Table1[Index]=0,Table1[Amount],LOOKUPVALUE(Table1[Amount],Table1[Index],Table1[Index]-1))
For more details, you can review the example in the attached PBIX file.
Reference: http://ignoringthevoices.blogspot.sg/2016/02/comparing-with-previous-row-using.html
Thanks,
Lydia Zhang