This topic contains 5 replies, has 2 voices, and was last updated by 
-
Posts
-
We conduct annual physical inventories and adjust during the day of the count and then the store has the next 30 days to make additional adjustments to correct invenotry.
I have 2 tables of adjustments (one on the day, and the other for the next 30 days).
I want a distinct count of parts that between the two tables do not net to zero.. In other words, I only want to count final parts that still have variance.
I used a brige table to related the two tables,
I created CALCULATE M2M in both tables that summed the quantity adjusted in both respective tables…
then neted those two qty CALC measures together.
A final measure looked at the netting measure and CALC DISTINCT COUNT (, filter) where qty <> 0. I could not get this final measure to work without an error.
As an alternative, I did a logical formula in the middle that looked at the netting formula to rule out/or ignore, and said if qty = 0 then 1, otherwise 0. Then a final formula that said IF=1 then 0, otherwise give me the net qty.
This works as a workaround, but is there a better way of doing this?
Thanks!
Can you create a relationship between the two tables on part or item ID?
Hello Mark,
Just corrected a typo in my first comment, should make more sense. Please advise.
Yes, I have a relationship based on unique part number. The two tables however have M2M relationships as we might have adjusted the same part more than once during the count. I use a unique table in the middle to relate the two tables to, and then use CALCULATE to pass through the other table.. that lets me work with M2M.
Hello Mark,
Your bridge table offers an interesting option. Many 2 Many is unnecessary. See attached workbook.
Although there are only a few parts (3 to be exact), the principle applies also to large tables.
The workbook is for Excel 2013, if you have 2010 I can attach a 2010 workbook also.
Thats great! Thanks so much!
The forum ‘Power Pivot’ is closed to new topics and replies.