Hi All,
My brain is fried again. I have scenario where I have to calculate various stats between two sets of data. The only thing common between the two data sets are the Calendar Weeks and the Supplier Numbers.
So I have two tables:
Table 1 has all of the supplier claims quantities by calendar week (different numbers of line items by supplier and CW).
Table 2 has all of the inventory by supplier and calendar week (different number of line items by inventory and CW).
I am able to develop a measure in Table 1 which Sums the inventory quantities in Table 2. (in the lower calculation area in Power Pivot) but this number does not change when I apply it to charts or even when I filter Table 1 because it is simply the sum of a inventory column from Table 2.
What I need this measure to do, is update when I filter Table 1 by the supplier or the calendar week or any other column. I've tried Filters, AllExcept, and a variety of other things. The two tables are joined by a separate relational table which ties their Calendar Weeks and Supplier numbers together. So ideally, if I filter Table 1 by supplier number, the measure should update the Sum of the inventory from Table 2 for that supplier but it doesn't. I can't get my mind around how to do it. What is the correct approach here?
Thank you!
My brain is fried again. I have scenario where I have to calculate various stats between two sets of data. The only thing common between the two data sets are the Calendar Weeks and the Supplier Numbers.
So I have two tables:
Table 1 has all of the supplier claims quantities by calendar week (different numbers of line items by supplier and CW).
Table 2 has all of the inventory by supplier and calendar week (different number of line items by inventory and CW).
I am able to develop a measure in Table 1 which Sums the inventory quantities in Table 2. (in the lower calculation area in Power Pivot) but this number does not change when I apply it to charts or even when I filter Table 1 because it is simply the sum of a inventory column from Table 2.
What I need this measure to do, is update when I filter Table 1 by the supplier or the calendar week or any other column. I've tried Filters, AllExcept, and a variety of other things. The two tables are joined by a separate relational table which ties their Calendar Weeks and Supplier numbers together. So ideally, if I filter Table 1 by supplier number, the measure should update the Sum of the inventory from Table 2 for that supplier but it doesn't. I can't get my mind around how to do it. What is the correct approach here?
Thank you!
