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.
Hello, I am trying to subtract values from 2 columns from 2 different tables. When I use SUM(column1)- SUM(column2), it is subtracting the values but it ignores the date value in the table and ends up subtracting everything. I want to remove Qty from table 1 that are in table 2 with date still considered. Please attached the data sample below:
Table 1 | ||
Item | Qty | Date |
111 | 10 | 2/25/2022 |
112 | 21 | 2/25/2022 |
111 | 15 | 2/26/2022 |
112 | 23 | 2/26/2022 |
Table 2 | ||
Item | Qty | Date |
111 | 5 | 2/25/2022 |
112 | 7 | 2/25/2022 |
111 | 3 | 2/26/2022 |
112 | 10 | 2/26/2022 |
Required output | ||
Item | Qty | Date |
111 | 5 | 2/25/2022 |
112 | 14 | 2/25/2022 |
111 | 12 | 2/26/2022 |
112 | 13 | 2/26/2022 |
I would really appreciate the help from the community!
Thank you in advance !
Solved! Go to Solution.
Hi @Anonymous ,
Using a date table and a table with an item column and creating a relationship with the original table is a workable solution.
For example. First create two calculated tables.
Date = CALENDAR(MIN('Table 1'[Date]),MAX('Table 2'[Date]))
Table 3 = VALUES('Table 1'[Item])
Then create the following relationship.
If you want the calculated column.
Column =
VAR _qty_1 = CALCULATE(SUM('Table 1'[Qty]))
var _qty_2 = CALCULATE(SUM('Table 2'[Qty]))
return
_qty_1-_qty_2
If what you want is a measure, you can use your original expression and drag it into the visual along with columns
Attach the PBIX file for reference. Hope it helps.
Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Hi @Anonymous ,
Using a date table and a table with an item column and creating a relationship with the original table is a workable solution.
For example. First create two calculated tables.
Date = CALENDAR(MIN('Table 1'[Date]),MAX('Table 2'[Date]))
Table 3 = VALUES('Table 1'[Item])
Then create the following relationship.
If you want the calculated column.
Column =
VAR _qty_1 = CALCULATE(SUM('Table 1'[Qty]))
var _qty_2 = CALCULATE(SUM('Table 2'[Qty]))
return
_qty_1-_qty_2
If what you want is a measure, you can use your original expression and drag it into the visual along with columns
Attach the PBIX file for reference. Hope it helps.
Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |