Forum Discussion
Calculating multiple columns from two different tables
Hi CA8172
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
I have a solution as below:
Assume your data is like
In Edit queries,
1. unpivot other columns for "purchased" column, the same for "sold"
2. Rename columns, then groupby columns
The same for " Sold" table(Table 2)
3. Then merge two tables, expand value
Finally, close&&apply, create a measure
Inventory = SUM('Table 1'[purchased value new])-SUM('Table 1'[Table 2.sold Value])Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It shouldn't break the merge. It sounds like an issue with the model. Likely you have a one to many relationship with a third table and a duplicate record was added in since the data was last loaded in making it no longer a one to many but a many to many.
Idk if that is actually the same product or not, but either way you should probably try to fix it so the data is accurate otherwise it'll still be an issue or cause inaccuracies.
If they're the same, grouping by Product will do the trick. If not, you'll want to rename it or use a different Unique ID.
PANDAmonium- Should I remove any & all Relationships prior to creating the Unpivoted Columns?
No. If the relationships should be there then keep them. Just fix the duplicate record. Either sum, remove, or rename it. However it's supposed to be so your data is accurate.