Multiply Table by a Reference Table With Conditions

[Anonymous]

Hello!  I'm looking for a solution to multiply values in a main table with ones in a reference table.  My problem is very similar to this solved solution: Solved: Help with Multiply columns by reference table - Microsoft Fabric Community

However, I have one wrinkle that I can't seem to get around.  

The tables have, for lack of a better term, two keys. 

Basically, every location also has a condition.  What I want to do is multiply the reference value against the location that has the same condition

DESIRED VALUE = Location with same condition multiplied by the corresponding value in the reference table.  

I've joined the tables by the location column, but can't seem to write a measure that does what I need.  Below is where I get stuck.  I tried unpivoting the columns but that creates a many to many relationship due to multiple locations in a reference table, and I still get stuck on how to write the measure.  

Reference Table Attempt

Measure =
SUMX (
    'Main Table';
    'Main Table'[Value] * RELATED ( 'Reference Table'[???] )
)

Reference Table Unpivoted Attempt

Unpivot Measure =
SUMX (
    'Main Table';
    'Main Table'[Value] * RELATED ( 'Reference Table'[Value] (? WHERE Attribute = 'Main Table'[Condition] ?)
)

Below is sample data.  Note in the live data there are hundreds of conditions: 

Main Table: 

Location	Condition	Value
Uptown	A	10
Uptown	A	20
Uptown	B	30
Downtown	A	40
Downtown	B	50
Downtown	B	60

Reference Table (A and B are the same conditions as the above table): 

Location	A	B
Uptown	.5	.6
Downtown	.4	.7

Reference Table Unpviot A & B

Location	Attribute	Value
Uptown	A	.5
Uptown	B	.6
Downtown	A	.4
Downtown	B	.7

Expected result: 

Location	A	B
Uptown	35 (70 * .5)	18 (30 * .6)
Downtown	16 (40 * .4)	77 (110 * .7)

[TomMartens · Accepted solution]

Hey @Anonymous ,

this is my measure:

Measure = 
sumx(
    'Main Table'
    , var currentLocation = [Location]
    var currentCondition = [Condition ]
    return
    'Main Table'[Value] 
    * 
    CALCULATE( SUM( 'Reference Table Unpviot A & B'[Value] ) 
        , 'Reference Table Unpviot A & B'[Location] = currentLocation 
        , 'Reference Table Unpviot A & B'[Attribute] = currentCondition 
    )
)

This is a simple Matrix visual (build by the columns of the Main table):

Please check the expected result for Location Uptown and Condition A, it seems the provided data does not match with the expected result.

You can not create a measure that reacts on different columns, at least not if you want the solution fully dynamic, independent of the "number" of conditions, for this reason I use the unpivoted table, but do not try to create any relationship.
Please be aware that in my solution the Main Table and the "Reference Table Unpivot ..." are not related, this are the only two table of the model:

Hopefully, this provides what you are looking for.

Regards,

Tom

[Anonymous]

That got it @TomMartens !  Thanks an absolute ton!