Forum Discussion

azizshaikh's avatar
azizshaikh
Frequent Visitor
10 years ago
Solved

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   ...
  • Anonymous's avatar
    Anonymous
    10 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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