Filtering a Calculated Measure From Another Table

lager1001

Board Regular
Joined
May 17, 2019
Messages
70
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!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,113,928
Messages
5,545,080
Members
410,652
Latest member
Zot
Top