Multiplying between Tables

[jjmauras]

This seems like it should be an easy objective but I cannot figure it out. Any help is appreciated.

All I want to do is create a New Column that multiplies 2 different rows. The rows exist in two different Tables that have an existing relationship. Google has been no help. In my example below I want to multiply "Qty To Pick" by "Unit Price". The relationship is the "Identifier" which is the "Order No" concatenated with "Item ID". Thanks again.

[Anonymous]

Hi everyone, 

I need your support please, I have two tables as shown below for example and I need to multiply values from the two tables together where IDs are equal. Any ideas?

Table 1		Table 2
ID	Value	ID	Value
x	1	y	4
y	2	z	5
z	3	x	6

[Greg_Deckler]

Not sure if you meant "multiplies 2 different rows" or "2 different columns". First, you need to make sure both columns are numeric, which is suspect since "Qto To Pick" does not have a summation symbol in front of it. Next you will probably have to create a measure for "Unit Price" in the p21_view_oe_line table, something like SUM or AVERAGE('p21_view_oe_line'[Unit Price]). You could then create a column in the p21_view_mt_pix_tix_detail that would be something like "=[Qty To Pick] * 'p21_view_oe_line'[New Measure]" where New Measure is the measure you created. Might be able to get away with not create the measure and then the formula would be "=[Qty To Pick] * 'p21_view_oe_line'[Unit Price] 

Any chance you can post sample or mocked up data and the expected outcome?

[jjmauras]

Thank you but in this situation I don't think I can Sum or Average the Unit Price and get the desired results. Sorry I did mean "multiply columns". I also changed Qty To Pick as numeric but I'm no closer.

Another question I have is since there is a relationship why isn't the Related() function working. I could just pull over the Unit Price into the table with Qty To Pick then just multiply them in a new column. For Example why doesn't this work:

in the p21_view_mt_pix_tix_detail table create a new column:

=Related('p21_view_oe_line[Unit Price])

[jjmauras]

I figured out the problem. For future reference. I have a Many-to-One relationship between these tables.

One - p21_view_mt_pix_tix_detail

Many - p21_view_oe_line

I was unaware you can not call a Many table into a One table. I'm pulling everything into the Many table and that seems to be working for me.

[Anonymous · Accepted solution]

Just to clarify on 1 to many relationships:

* From the "Many" side, you can use RELATED(OneSideTable[Field]) just fine.  As there is... only 1 row that will match.

* From the "One" side, you can use RELATEDTABLE(ManySideTable) as get all rows associated with the one side... and do something w/ those rows.  eg:  =SUMX(RELATEDTABLE(ManySideTable), ManySideTable[Price])

[gbarredap]

I need a similar solution but I have Many to Many relationship.