Divide value according to category in other table

[Anonymous]

I have two tables, the first shows the products and their ratio. The second shows the product and their values. I wish to create a new column to calculate the converted value (value/ratio) according to the respective product category. I tried to make a one-to-many relationship from first table to second table by linking 'product'. However, it only allows many-to-many relationship and both direction. How to make it one-to-many relationship and single direction? Any help given is appreciated. Thank you.

First table:

 Second table:

Expected output:

[Whitewater100 · Accepted solution]

Hi:

I beleive answer is same (actually provide Calc Col and Measure). The main thing was to answer the relationship question. Please see attached for that part. I hope this helps..

https://drive.google.com/file/d/1n_q1I9SuwHTEZXTSTQDFjQnX39uG0BXl/view?usp=sharing 

Measure_Converted Value = SUMX(DATA, Data[Value] / Data[Ration])

[Anonymous]

Did you manually type in the Prod_ID column in the Data table? 

[SanketBhagwat]

Hi @Anonymous .

You can create a summary table using "SUMMARIZE'.
You can write your DAX as ;

Test =

SUMMARIZE(Table2,Table2[Product],Table2[Value],Table1[Equivalent Ratio])

Then create a calculated column for Converted Value using ;

Converted_Value =

DIVIDE(Test[Value],Test[Equivalent Ratio])


See below output;

Thanks,
Sanket


If this post helps, then mark it as "Accept as Solution" and give it a thumbs up.

[Anonymous]

sry but I am not sure why the table 1 does not show up in the summarize function