Filtering a Calculated Measure From Another Table


Board Regular
May 17, 2019
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

Latest member