Subtract Value from different column and row

[ShawnnaBee]

I'm a pretty new user to Power BI and have run into a calculation that is really simple in excel, but can't seem to get it to translate well into Power BI: 

Column A	Column B
2425	2425
9914	7489
20148	12659
36584	23925
46465	22540

I need to Subtract A3 from B2 and Report it in B3, A4 from B3 and Report it in B4, down the column. In Excel, I'd just type=A3-B2 in cell B3 and Drag...

Any help would be really appreciated!

[v-yingjl · Accepted solution]

Hi @ShawnnaBee ,

In power bi, you need to create an index column in query editor as a 'row number' first.

Then you can create this calculated column like this to get the result Column B:

Column B =
VAR tab =
    ADDCOLUMNS ( 'Table', "O/E", MOD ( 'Table'[Index], 2 ) )
RETURN
    ABS (
        SUMX (
            FILTER ( tab, [Index] <= EARLIER ( 'Table'[Index] ) && [O/E] = 1 ),
            [Column A]
        )
            - SUMX (
                FILTER ( tab, [Index] <= EARLIER ( 'Table'[Index] ) && [O/E] = 0 ),
                [Column A]
            )
    )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

what is the condition  of  the subtraction?  obviously this isn't excel and power bi isn't a spreadsheat  so you will have to give it coordinates, like you do in excel which is a3 - b2 (3 and 2 being the coordinates)

best thing to do is create another column with an index and call it ID and make sure the data is in the order you need it to be , you can add an index column in power query

and then you can create a measure that does something like this

variance =

var id1 = selectedvalue('Table'[id])

var id2 = selectedvalue('Table'[id]) - 1

var value1 = calculate(sum('Table'[value]), 'Table'[id] = id1)

var value2 = calculate(sum('Table'[value]), 'Table'[id] = id2)

return value1 - value2

this is just an example.  If you have other data you can use to create the pattern like date, etc you can use that too

see attached