Calculating multiple columns from two different tables

[CA8172]

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!

[CA8172]

@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!

[v-juanli-msft · Accepted solution]

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
Maggie

Community 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.

[PANDAmonium]

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!

[ChandeepChhabra]

Please download to the solution.

Hope this helps

Thanks

[PANDAmonium]

Values are different, but this is how it'll look like.

[PANDAmonium]

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

[CA8172]

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.

[PANDAmonium]

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.

[CA8172]

@PANDAmonium- Should I remove any & all Relationships prior to creating the Unpivoted Columns?

[PANDAmonium]

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.