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
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.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
