Getting Value from one Table into other to calculate

[Pfoster]

Hello, 
I am totally lost and do not find a solution:

I have two tables:

and 

Every CompMat can be produced out of 0 to 4 different Blendingproducts. To find the right Margin, I need to bring the value of the Measure [Focus_ST__CMA_per_MT] from 'Sales Data' into the 'Blend Master' table and multiply it with the value of 'Blend Master'[Content Blend 1]. To find the right value in 'Sales Data', I am using the Keys 'Blend Master'[ST_Key_Blend1]... 'Blend Master'[ST_Key_Blend4]. These keys are the same as 'Sales Data'[See-Through_Product]. 

I tryied to build the measure like this:

Focus_ST_Blend1 = 
VAR CurrentSTProduct = SELECTEDVALUE('Sales Data'[See-Through_Product])
VAR CurrentYear = SELECTEDVALUE('Sales Data'[Year])
VAR CurrentScenario = SELECTEDVALUE(FocusScenario[Focus Scenario])
RETURN
CALCULATE(
    [Focus_ST_CMA_per_MT],
    FILTER(
        ALL('Sales Data'),
        'Sales Data'[See-Through_Product] = CurrentSTProduct
    )
)

But the value I got, is totally wrong, and I do not understand, why. Where am I loosing track? 

[Pfoster · Accepted solution]

Found a solution:
via Merge, I got the information of Content_Blend into my "main" Table and then in a measure the values of the [Focus_ST_CMA_per_MT]. 
Now, it is working like it should. 

Sometimes, it helps to sleep over a problem... 

[DataNinja777]

Hi @Pfoster ,

Your original DAX formula isn't working because it's trying to find a value from within the 'Sales Data' table's context. The correct approach is to start from the 'Blend Master' table, iterate through its rows, and for each row, perform a lookup into the 'Sales Data' table to get the margin you need.

You can achieve this with a measure that combines the SUMX and CALCULATE functions. This single measure will handle all four possible blend products and add their contributions together for a final total.

Focus_ST_Blend_Margin_Total =
-- Calculate and sum the margin contribution for Blend 1
SUMX (
    'Blend Master',
    'Blend Master'[Content Blend 1] * CALCULATE (
        [Focus_ST_CMA_per_MT],
        'Sales Data'[See-Through_Product] = 'Blend Master'[ST_Key_Blend1]
    )
)
+
-- Calculate and sum the margin contribution for Blend 2
SUMX (
    'Blend Master',
    'Blend Master'[Content Blend 2] * CALCULATE (
        [Focus_ST_CMA_per_MT],
        'Sales Data'[See-Through_Product] = 'Blend Master'[ST_Key_Blend2]
    )
)
+
-- Calculate and sum the margin contribution for Blend 3
SUMX (
    'Blend Master',
    'Blend Master'[Content Blend 3] * CALCULATE (
        [Focus_ST_CMA_per_MT],
        'Sales Data'[See-Through_Product] = 'Blend Master'[ST_Key_Blend3]
    )
)
+
-- Calculate and sum the margin contribution for Blend 4
SUMX (
    'Blend Master',
    'Blend Master'[Content Blend 4] * CALCULATE (
        [Focus_ST_CMA_per_MT],
        'Sales Data'[See-Through_Product] = 'Blend Master'[ST_Key_Blend4]
    )
)

This DAX works because SUMX iterates through each row of the 'Blend Master' table, creating a row context. For each of those rows, CALCULATE temporarily modifies the filter context to find the correct [Focus_ST_CMA_per_MT] value. It does this by using the key from the current row ('Blend Master'[ST_Key_Blend1]) to filter the 'Sales Data' table. Critically, this method preserves existing report filters like Year and Scenario, which your original attempt using ALL() removed. The formula then multiplies the found margin by the blend content for that row and finally sums up the results from all rows and all four blend calculations to give you the correct total margin contribution.

Best regards,

[Pfoster]

Had to fixed your Measure with a Var, because the part 
"

'Sales Data'[See-Through_Product] = 'Blend Master'[ST_Key_Blend

is not working. The fixed one is looking like:

Focus_ST_Blend_Margin_Total = 
-- Calculate and sum the margin contribution for Blend 1
(SUMX (
    'Blend Master',
    VAR KeyBlend1 = 'Blend Master'[ST_Key_Blend1]
    RETURN
    'Blend Master'[Content Blend 1] * CALCULATE (
        [Focus_ST_CMA_per_MT],
        'Sales Data'[See-Through_Product] = KeyBlend1
    )
)
+
-- Calculate and sum the margin contribution for Blend 2
SUMX (
    'Blend Master',
    VAR KeyBlend2 = 'Blend Master'[ST_Key_Blend2]
    RETURN
    'Blend Master'[Content Blend 2] * CALCULATE (
        [Focus_ST_CMA_per_MT],
        'Sales Data'[See-Through_Product] = KeyBlend2
    )
)
+
-- Calculate and sum the margin contribution for Blend 3
SUMX (
    'Blend Master',
   VAR KeyBlend3 = 'Blend Master'[ST_Key_Blend3]
    RETURN
    'Blend Master'[Content Blend 3] * CALCULATE (
        [Focus_ST_CMA_per_MT],
        'Sales Data'[See-Through_Product] = KeyBlend3
    )
)
+
-- Calculate and sum the margin contribution for Blend 4
SUMX (
    'Blend Master',
    VAR KeyBlend4 = 'Blend Master'[ST_Key_Blend4]
    RETURN
    'Blend Master'[Content Blend 4] * CALCULATE (
        [Focus_ST_CMA_per_MT],
        'Sales Data'[See-Through_Product] = KeyBlend4
    )
))*[FocusMT]

but is still not delivering any value.... have to think about it. 

[Pfoster]

yes, of course:

Focus_ST_CMA_per_MT = 
VAR CurrentSTProduct = SELECTEDVALUE(SeeThroughProducts[See-Through_Product])
VAR CurrentYear = SELECTEDVALUE('Sales Data'[Year])
VAR CurrentScenario = SELECTEDVALUE(FocusScenario[Focus Scenario])
RETURN
CALCULATE(
    [ST_CMA_per_MT],
    FILTER(
        ALL(SeeThroughProducts),
        SeeThroughProducts[See-Through_Product] = CurrentSTProduct
    )
)

[Abhilash_P]

Hi @Pfoster ,
Firstly you cannot bring measure from one table to another insead you can create extra column in Blend Master and brind the values from sales data table before that 
Can you share the logic for  Measure [Focus_ST__CMA_per_MT]