Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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!
Solved! Go to 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.
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.
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
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
Proud to be a Super User!
User | Count |
---|---|
89 | |
88 | |
84 | |
81 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |