Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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
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 .
@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
@amitchandak , actually these are not columns, these are measures generated in power bi.
Any idea to work with measures and generate new measures?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |