topic Re: Multiplying 1 column by another - Hopefully straight forward and I am being silly in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3085230#M107783 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="317289" data-lia-user-login="tamerj1" class="lia-mention lia-mention-user">tamerj1</a>&nbsp;,</P><P>&nbsp;</P><P>Apologies this is in fact correct you're a magician!</P><P>&nbsp;</P><P>Many thanks once again!</P><P>&nbsp;</P><P>Taylor</P> Fri, 17 Feb 2023 14:52:12 GMT Ttaylor9870 2023-02-17T14:52:12Z Multiplying 1 column by another - Hopefully straight forward and I am being silly https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3084771#M107732 <P>Hi All,</P><P>&nbsp;</P><P>I have 1 column in my Nav_Item table called average selling price and I would like to multiply this by my Free Stock Value measure. For some reason when I perform this return answer is wrong... Anyone have any ideas? For example...</P><P>&nbsp;</P><TABLE border="1"><TBODY><TR><TD>Customer Name</TD><TD>Product Name</TD><TD>Average Selling Price</TD><TD>Free Stock Quantity</TD><TD>Stock Value</TD></TR><TR><TD>Bob</TD><TD>Apples</TD><TD>£98.12</TD><TD>10,208.44</TD><TD>£250,412.93</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>Measure for <EM><STRONG>Free Stock Quantity </STRONG></EM>is using <EM><STRONG>Total Quantity</STRONG></EM> measure from my Nav_ItemLedger - <EM><STRONG>Total TOB Approved Unposted&nbsp;</STRONG></EM>from my Nav_SalesLine table here are the measures below...</P><P>&nbsp;</P><DIV><DIV><SPAN>Free Stock Quantity = Nav_ItemLedger</SPAN><SPAN>[Total Quantity]</SPAN><SPAN> - </SPAN><SPAN>[Total TOB Approved Unposted]</SPAN></DIV><DIV>&nbsp;</DIV></DIV><DIV><DIV><SPAN>Total Quantity = </SPAN><SPAN>Calculate</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>'NAV_Item Ledger Entry'</SPAN><SPAN>[Quantity]</SPAN><SPAN>))</SPAN></DIV><DIV>&nbsp;</DIV></DIV><DIV><DIV><SPAN>Total TOB Approved Unposted = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>('NAV_Sales Line'[Outstanding Quantity]),'NAV_Sales Line'[Location Code]=</SPAN><SPAN>"TOB"</SPAN><SPAN>)</SPAN></DIV></DIV><P>&nbsp;</P><P>Hopefully this is explained effectively and long story short...</P><P>&nbsp;</P><P><SPAN>'NAV_Item Ledger Entry'</SPAN><SPAN>[Quantity]</SPAN></P><P>&nbsp;</P><P><SPAN>...subtract...</SPAN></P><P>&nbsp;</P><P><SPAN>'NAV_Sales Line'[Outstanding Quantity])</SPAN></P><P>&nbsp;</P><P>= Free stock Quantity and then get this Free stock Quantity * Average Sales Price....</P><P>&nbsp;</P><P>Many Thanks,</P><P>&nbsp;</P><P>Taylor <span class="lia-unicode-emoji" title=":smiling_face_with_smiling_eyes:">😊</span></P> Fri, 17 Feb 2023 10:22:00 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3084771#M107732 Ttaylor9870 2023-02-17T10:22:00Z Re: Multiplying 1 column by another - Hopefully straight forward and I am being silly https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3084818#M107737 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="415490" data-lia-user-login="Ttaylor9870" class="lia-mention lia-mention-user">Ttaylor9870</a>&nbsp;<BR />Please try</P> <LI-CODE lang="javascript">NewMeasure = SUMX ( Nav_Item, Nav_Item[average selling price] * [Free Stock Value measure] )</LI-CODE> Fri, 17 Feb 2023 10:37:18 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3084818#M107737 tamerj1 2023-02-17T10:37:18Z Re: Multiplying 1 column by another - Hopefully straight forward and I am being silly https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3084949#M107755 <P>Hi <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="317289" data-lia-user-login="tamerj1" class="lia-mention lia-mention-user">tamerj1</a>&nbsp;, Thanks for replying!</P><P>&nbsp;</P><P>This still gives the wrong answer, I'd assume this should give the correct one so it may be down to my data model then? I've tried everything and still can't manage to catch it.</P> Fri, 17 Feb 2023 12:07:59 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3084949#M107755 Ttaylor9870 2023-02-17T12:07:59Z Re: Multiplying 1 column by another - Hopefully straight forward and I am being silly https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3084979#M107759 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="415490" data-lia-user-login="Ttaylor9870" class="lia-mention lia-mention-user">Ttaylor9870</a>&nbsp;<BR />Can you share a dummy sample file?</P> Fri, 17 Feb 2023 12:26:14 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3084979#M107759 tamerj1 2023-02-17T12:26:14Z Re: Multiplying 1 column by another - Hopefully straight forward and I am being silly https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3085024#M107761 <P>Here is the relationship below...</P><P><img /></P><P>&nbsp;</P><P>And here are the tables...</P><P>Nav_Sales Line:</P><P>No_Production GroupAverage Selling PriceProduct NameDocument No_Outstanding Quantity</P><TABLE><TBODY><TR><TD>ABC</TD><TD>Lego</TD><TD>1</TD><TD>Blue</TD><TD>SO1234</TD><TD>33</TD></TR><TR><TD>ABC</TD><TD>Lego</TD><TD>2</TD><TD>Green</TD><TD>SO1235</TD><TD>44</TD></TR><TR><TD>ABC</TD><TD>Lego</TD><TD>3</TD><TD>Gold</TD><TD>SO1236</TD><TD>55</TD></TR><TR><TD>ABC</TD><TD>Lego</TD><TD>4</TD><TD>Black</TD><TD>SO1237</TD><TD>66</TD></TR><TR><TD>ABC</TD><TD>Lego</TD><TD>5</TD><TD>Silver</TD><TD>SO1238</TD><TD>77</TD></TR><TR><TD>JJJ</TD><TD>Cars</TD><TD>1</TD><TD>Red</TD><TD>SO1239</TD><TD>22</TD></TR><TR><TD>JJJ</TD><TD>Cars</TD><TD>2</TD><TD>Blue</TD><TD>SO1231</TD><TD>33</TD></TR><TR><TD>JJJ</TD><TD>Cars</TD><TD>3</TD><TD>Grey</TD><TD>SO1232</TD><TD>11</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Nav_Item:</P><P>No_Production GroupAverage Selling PriceProduct Name</P><TABLE><TBODY><TR><TD>ABC</TD><TD>Lego</TD><TD>1</TD><TD>Spiderman</TD></TR><TR><TD>JJJ</TD><TD>Cars</TD><TD>2</TD><TD>Optimus Prime</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Nav_Item Ledger Entry</P><P>Item NoQuantity</P><TABLE><TBODY><TR><TD>ABC</TD><TD>2</TD></TR><TR><TD>ABC</TD><TD>3</TD></TR><TR><TD>ABC</TD><TD>6</TD></TR><TR><TD>JJJ</TD><TD>7</TD></TR><TR><TD>JJJ</TD><TD>2</TD></TR><TR><TD>JJJ</TD><TD>3</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Measures I have used so far...</P><DIV><DIV><SPAN>Total TOB Approved Unposted = </SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>'NAV_Sales Line'[Outstanding Quantity]</SPAN><SPAN>)</SPAN></DIV><DIV><DIV><DIV><SPAN>Total Quantity = </SPAN><SPAN>Calculate</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>'NAV_Item Ledger Entry'[Quantity]</SPAN><SPAN>))</SPAN></DIV><DIV><DIV><DIV><SPAN>Free Stock Quantity = </SPAN><SPAN>'Nav_Item Ledger Entry'</SPAN><SPAN>[Total Quantity]</SPAN><SPAN> - </SPAN><SPAN>[Total TOB Approved Unposted]</SPAN></DIV><DIV><DIV><DIV><SPAN>Free Stock Value = </SPAN><SPAN>SUMX</SPAN><SPAN> ( </SPAN><SPAN>Nav_Item</SPAN><SPAN>, </SPAN><SPAN>Nav_Item[average selling price]</SPAN><SPAN> * </SPAN><SPAN>[Free Stock Quantity]</SPAN><SPAN> )</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>Example table of the putcome...</SPAN></DIV><DIV><img /><P>&nbsp;</P></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV> Fri, 17 Feb 2023 13:21:31 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3085024#M107761 Ttaylor9870 2023-02-17T13:21:31Z Re: Multiplying 1 column by another - Hopefully straight forward and I am being silly https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3085089#M107765 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="415490" data-lia-user-login="Ttaylor9870" class="lia-mention lia-mention-user">Ttaylor9870</a>&nbsp;<BR />Please clarify why do you believe the number is not correct.</P> <P><img /></P> Fri, 17 Feb 2023 13:58:32 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3085089#M107765 tamerj1 2023-02-17T13:58:32Z Re: Multiplying 1 column by another - Hopefully straight forward and I am being silly https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3085230#M107783 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="317289" data-lia-user-login="tamerj1" class="lia-mention lia-mention-user">tamerj1</a>&nbsp;,</P><P>&nbsp;</P><P>Apologies this is in fact correct you're a magician!</P><P>&nbsp;</P><P>Many thanks once again!</P><P>&nbsp;</P><P>Taylor</P> Fri, 17 Feb 2023 14:52:12 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Multiplying-1-column-by-another-Hopefully-straight-forward-and-I/m-p/3085230#M107783 Ttaylor9870 2023-02-17T14:52:12Z