Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
ShawnnaBee
New Member

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 AColumn B
24252425
99147489
2014812659
3658423925
4646522540

 

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!

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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]
            )
    )

re.png

 

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.

View solution in original post

2 REPLIES 2
v-yingjl
Community Support
Community Support

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]
            )
    )

re.png

 

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
Super User
Super User

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

 

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.