Column Name as a reference to another reference table

[Anonymous]

Hi Team,

Good Day !

I am wondering if this scenario can be done using dax by creating measure

I have table 1 which has customer name and list of products availed and another reference table that has product cost and my goal is to compute for the total product availed

Here are the tables

Table 1

Customer Name	Product 1	Product 2	Product 3
John Doe	12	10	1
Hello World	10	3	4

ProductsTable

Product Name	Product Desc	Product Cost
Product 1	blah blah	$30
Product 2	blahabalah	$25
Product 3	blah blah	$19

I would like to add a measure in table 1 with 

TotalProductCost = SUM(('Table1'[Product1]*'ProductsTable'[ProductCost]), ('Table1'[Product2]*'ProductsTable'[ProductCost]),

('Table1'[Product3]*'ProductsTable'[ProductCost]))

How can I get the specific cost for each product using the column name as reference from table1?

Is this possible?

Thanks in advance

Cheers!

John

[ryan_mayu]

@Anonymous 

i suggest you to transform the data in table 1

select customer name column and unpivot other columns

then create relationships between two table via product columns

that will be easier for you to do the further calculation