Forum Discussion
Multiplying between Tables
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.
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])
6 Replies
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?
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])
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.
