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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Watch MrExcel Video

Forum statistics

Threads
1,113,978
Messages
5,545,302
Members
410,675
Latest member
DLongmountain
Top