Forum Discussion
Column Name as a reference to another reference table
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
1 Reply
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
