CALCULATE VALUE: applying Looked-up values filter

Hi,

Here is an extract of the sales table

Here is an extract of the delivery table

The table are linked between each other through intermediary matrix used as key connector since many-to-many are not allowed:

• Material 
• Sales document & reference document
• Sales unit

the desired table is a multi-column table that would display the following:

• material 
• Order qty:=SUM(sales[Order Qty(item)]
• Shipped:= TO BE DETERMINED
• Balance:= [Order Qty]-[Shipped]

Please note that beside the Material, all other entries of the table are calculated field. Ultimately, the goal would be to use the Balance measure in the same table context but with other calculated values for further development.

As of now the detail result is as following:Table 1The Shipped measure used is currently as following:

shipped:=CALCULATE(
                   SUM('Delivery'[Delivery quantity]),
                       FILTER('Delivery',
                              COUNTROWS(
                                        VALUES(SALES[Sales document]))>0))

As you can see the formula works perfectly at a row context level when the Sales # are added to the table. But the formula does not function anymore when the Sales # are removed to meet the desired table context (table 2). I'm not supposed to get negative balance since the qty shipped cannot exceed the qty ordered. 

In addition, the totals in table 1 are not aligned with the displayed information. This is due to the fact that there is more value in the delivery table and that the filter I'm applying is not effective at all. I've also tried with HASONEVALUE() but the result is not better. the current filter is only effective in term of filtering the row display display in table 1 to show only the desired information, The current filter does not apply a filter on the calculated values themselves. Therefore, when the desired table context is prompted the following result occurs:

Table 2Just like the grand total of table 1, each line of the desired table displays the total of the qty shipped per Material regardless of the condition of the filter. Hence the need for a better filter. If we go through the same information as table 2 adding the sales # in the table context (table 3), we discover that actually a total of shipped Qty is about 252, thus a balance 22.

Table 3

To make it clear, the filter condition for the sum of qty shipped should be: 'consider only the value for which the sales # that are listed in both table'. Hence only 4 distinct Sales # should be considered for the calculation, prompting thus the correct total regardless of the table context (w/o sale #), and the total should be coherent (table 4).Table 4. Desired result

When I started to work on this with a Basic EXCEL point of view, LOOKUPVALUE() seemed perfect for the job but turns out we cannot "call" the sum of a column. the reason why is well understood and documented. But I still need to get the job done, despite trying the work arround with HASONEVALUE() or COUNTROW()>0.

If I approach now this problem with a SQL point of view, the logic want that I'd do an inner join query based on the common sales ref between the two tables to get the desired information out of the delivery table. Therefore, I think the DAX formula might run around a filter that create a table with an INNER JOIN kind of structure to use as such, but I still need to get it to work without error:

Shipped:=CALCULATE(
                  SUM('Delivery'[Delivery quantity]),
                     FILTER(
                           VALUES('Delivery'[Reference document]),
                                 NATURALINNERJOIN(
                                                 VALUES(SALES[Document reference]),
                                                       VALUES('Delivery'[Reference document]))))

In anticipation of your future questions:

• No, I don't want to create a new table, Dax should iterate through a virtual table.
• Yes, I'd like to hold to my measure as much as possible to keep my data model optimized 

I hope my additional information have been exhaustive enough.

Please let me know your take on this, I'm pretty sure I'm not close to get a solution quite yet.

Most importantly, do indicate me where I'm mistaken in my approach.

Thank you for your time,

Kalo