Forum Discussion
create new measure using Sum, subtract and filter multiple measures across two different tables
Hi all,
I'm a newbie in power bi and would like to create a measure where I need to use the simple sum and subtract formula to do it using multiple measures across two different tables and filter it down for specific values.
The formula is that: A + B + C - D + E+F
The data looks like that but all of these fields are based on the created measures in PBI. The first table (Table 1) looks like that: -
| A | F | D |
| € 16,79 | € 4,65 | |
| € 84,90 | ||
| € 86,95 | ||
| € 64,95 | ||
| € 64,95 | € 3,99 | |
| € 64,95 | € 3,99 | |
| € 64,95 | € 3,99 | |
| € 99,90 | ||
| € 199,80 | ||
| € 54,90 | € 5,00 | € 3,99 |
| € 49,95 |
The first table (Table 2) looks like that: -
| Type | B | C | E |
| Order | -€ 6,37 | -€ 2,34 | -€ 4,46 |
| Order | -€ 9,44 | -€ 12,74 | |
| Order | -€ 13,04 | ||
| Order | -€ 7,01 | -€ 9,74 | |
| Order | -€ 7,01 | -€ 9,74 | |
| Order | -€ 7,01 | -€ 9,74 | |
| Order | -€ 7,01 | -€ 9,74 | |
| Refund | -€ 7,39 | -€ 14,99 | |
| Refund | -€ 14,78 | -€ 29,98 | |
| Refund | -€ 5,52 | -€ 7,49 | |
| Refund | -€ 5,52 | -€ 7,49 |
I tried earlier with a couple of different ways but was not able to reach the end output.
Sum order = Table 1[A] + Table 2[B] - Table 2[] + Table 1[] + ...,
filter(Table 2,Table 2[Type] = "Order"))
Can you please guide me, what is the best way to achieve it by using measures?
Thanks in advance.
Regards,
Ahsan
Anonymous , if you have common dimensions you can create measure like
Sum(Table1[A]) +Sum(Table2[B]) + Sum(Table2[C]) - Sum(Table1[D]) + E+Sum(Table1[F])
if not then if you common column and you want to merge the table in power query Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Or move a column from one table to another
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
4 Replies
- amitchandak
Super User
Anonymous , if you have common dimensions you can create measure like
Sum(Table1[A]) +Sum(Table2[B]) + Sum(Table2[C]) - Sum(Table1[D]) + E+Sum(Table1[F])
if not then if you common column and you want to merge the table in power query Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Or move a column from one table to another
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8amitchandak , actually these are not columns, these are measures generated in power bi.
Any idea to work with measures and generate new measures?
v-zhangtiCommunity Support
Hi, Anonymous
Do Table 1 and Table 2 have unique associated values? Otherwise it is impossible to determine which row should be added and subtracted. Please provide more information, thank you.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-zhangti , yes these two tables are associated with a unique key. I tried multiple approaches and found a way as described by amitchandak .