Multiply 2 Columns By Row Context

[qwerrrewq]

Hello, 

I tried to search in another posts to find solution without success...

I have Data Model with 2 Tables.

Table1 Contains Sales Data, Table2 Contains Sales Goals.

Every Product In Table1 belongs to a ProductGroup.

There Is Relationship Between Tables Based on The ProductGroup Column.

I Want to create a measure that multiply the Total SalesAmount From Table1 in the SalesGoal From Table2 by their ProductGroup Context, For Example:

I Want the apple&avocado&lemon Total SalesAmount to be Multiplied in 10% Because this is their Product Group Goal.

Same Logic For The tomato&apricorn and etc.

Thanks In Advance

Below i Attached A Screenshot:

https://imgur.com/E02ZsFg 

[V-lianl-msft · Accepted solution]

Hi @qwerrrewq ,

Based on the data provided by you, I have created the following DAX to get the expected results:

Measure =
VAR sum_group =
    CALCULATE (
        SUM ( Table1[SalesAmount] ),
        ALLEXCEPT ( Table1, Table1[ProductGroup] )
    )
RETURN
    CALCULATE (
        sum_group * MAX ( Table2[ProductGroup Sales Goal] ),
        CROSSFILTER ( Table2[ProductGroup], Table1[ProductGroup], BOTH )
    )

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.