topic Divide value according to category in other table in DAX Commands and Tips

Divide value according to category in other table

Anonymous — Tue, 10 May 2022 05:46:07 GMT

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:

Re: Divide value according to category in other table

Anonymous — Tue, 10 May 2022 06:17:11 GMT

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.

Re: Divide value according to category in other table

Anonymous — Tue, 10 May 2022 09:03:02 GMT

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

Re: Divide value according to category in other table

Whitewater100 — Tue, 10 May 2022 12:53:45 GMT

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])

Re: Divide value according to category in other table

Anonymous — Wed, 11 May 2022 01:14:58 GMT

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