Forum Discussion
Calculating multiple columns from two different tables
I have two tables (see below), the first table has "Items" Purchased by "Store", the second table has "Items" Sold by "Store".
I would like to calculate in a new table (see below) the leftover Inventory for each "Item" by respective "Store".
I tried using = IF(RELATED('Purchased'[Items]) = RELATED(Sold[Items]), CALCULATE(SUM('Purchased'[Store 1]) - CALCULATE(SUM(Sold[Store 1])))) --- that works, but when I try to do the same thing for the other five remaining stores, I get the following error message: A circular dependency was detected. I tried to use USERELATIONSHIP and some other searches within Google, none of which really helped me address what I was needing, so any assistance would be greatly appreciated. As we will be refreshing the excel file of Purchased and Sold each month, so we would like these 'Inventory' calculations to stay within PBI.
Thanks!
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.
10 Replies
In Power Query:
Transform both matrices into tables. So for both tables...
- Raname Purchased / Sold headers for your first column with Product
- Highlight Stores 1 - 6 columns and under Transform, select Unpivot Columns
- It'll convert it into two columns, essentially Store and Purchased / Sold amounts once you rename the headers
Then you'll want to merge in your Sold table into your purchased table so...
- While in your Purchased table, go to Home > Merge Queries dropdown > Merge Queries as new
- Highlight both Product and Store columns.
- Select your Sold table for the merge and highlight the same columns.
Lastly create a new column for Inventory which is Purchased - Sold
It looks like I was not aware that there were some duplicate Products in each of the tables and when I went to apply the appended queries, etc. I got the below error message. Is there any other way around this OR do we need to rename some of the duplicate Products so they can be identified differently?
Apply query changes
Merge1
Load was cancelled by an error in loading a previous table.
Purchased
Column 'Products' in Table 'Purchased' contains a duplicate value 'Z001-702' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
Sold
Column 'Products' in Table 'Sold' contains a duplicate value 'Z001-702' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
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.
Values are different, but this is how it'll look like.
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.Hi,
Thanks for helping explain that in screenshots. Though they still have to fix the issue with duplicate products. Your/their duplicates records at the same store are doubling purchased when expanded. See product Z at store 1 for reference. So without fixing the duplicates, it's doing: (purchased * 2) - sales = inventory which isn't accurate.
So they still have to either sum, remove, or rename those duplicate products depending on what accuratly reflects the data. Hopefully you can help them through this last step as well.
Thanks!
PANDAmonium - I greatly appreciate your help, but I ended up using v-juanli-msft as the Accepted Solution since she had provided the screen shots and template of how she built it, so it was easier for me to duplicate since I appeared to have missed or messed up on a step in your process. Thank you all for your help!
