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.

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
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.