Forum Discussion

ShawnnaBee's avatar
ShawnnaBee
New Member
5 years ago
Solved

Subtract Value from different column and row

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 99...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
5 years ago

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