topic Re: Scrap Ratio (Dax formula that uses data from two different tables) in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Scrap-Ratio-Dax-formula-that-uses-data-from-two-different-tables/m-p/2939289#M97019 <P>I need the information to stay on the table. Power Bi will sum the scrap, but it is not summering my labels made. I can only assume it is because of the mass quantity of data, but that does not make sense. I tried clearing all filters, but that did not fix the problem. I included a little more detail about my problem.</P><P>&nbsp;</P><P>Scrap Made</P><TABLE><TBODY><TR><TD>Material</TD><TD>Quantity</TD><TD>Scrapping date</TD></TR><TR><TD>Q339</TD><TD>0.600</TD><TD>11/4/2022</TD></TR><TR><TD>Q966</TD><TD>-88.540</TD><TD>11/4/2022</TD></TR><TR><TD>Q231</TD><TD>-115</TD><TD>11/4/2022</TD></TR><TR><TD>Q545</TD><TD>-22.480</TD><TD>11/4/2022</TD></TR><TR><TD>S318</TD><TD>809</TD><TD>11/4/2022</TD></TR><TR><TD>Q407</TD><TD>-4,146</TD><TD>11/4/2022</TD></TR><TR><TD>Q239</TD><TD>-117.896</TD><TD>11/4/2022</TD></TR><TR><TD>Q484</TD><TD>-1,457.080</TD><TD>11/4/2022</TD></TR><TR><TD>Q958</TD><TD>-501.530</TD><TD>11/4/2022</TD></TR><TR><TD>A354</TD><TD>-450</TD><TD>11/4/2022</TD></TR><TR><TD>L160</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L350</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L314</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L314</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L900</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L640</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L630</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L480</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L190</TD><TD>3</TD><TD>11/4/2022</TD></TR><TR><TD>L200</TD><TD>2</TD><TD>11/4/2022</TD></TR><TR><TD>L350</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L470</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>B042</TD><TD>50</TD><TD>11/4/2022</TD></TR><TR><TD>L240</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L316</TD><TD>2</TD><TD>11/4/2022</TD></TR><TR><TD>L320</TD><TD>2</TD><TD>11/4/2022</TD></TR><TR><TD>L500</TD><TD>12</TD><TD>11/4/2022</TD></TR><TR><TD>L490</TD><TD>12</TD><TD>11/4/2022</TD></TR><TR><TD>L430</TD><TD>12</TD><TD>11/4/2022</TD></TR><TR><TD>L500</TD><TD>12</TD><TD>11/4/2022</TD></TR><TR><TD>L490</TD><TD>12</TD><TD>11/5/2022</TD></TR><TR><TD>L430</TD><TD>12</TD><TD>11/5/2022</TD></TR><TR><TD>L160</TD><TD>16</TD><TD>11/5/2022</TD></TR><TR><TD>L350</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L314</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L314</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L900</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L640</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L630</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L480</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L190</TD><TD>3</TD><TD>11/5/2022</TD></TR><TR><TD>L200</TD><TD>2</TD><TD>11/5/2022</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>Labels Made</P><TABLE><TBODY><TR><TD>Material</TD><TD>Quantity</TD><TD>Scrapping date</TD></TR><TR><TD>Q339</TD><TD>0.600</TD><TD>1/4/2022</TD></TR><TR><TD>Q966</TD><TD>796.860</TD><TD>1/4/2022</TD></TR><TR><TD>Q231</TD><TD>345</TD><TD>1/4/2022</TD></TR><TR><TD>Q545</TD><TD>899.200</TD><TD>1/4/2022</TD></TR><TR><TD>S318</TD><TD>809</TD><TD>1/4/2022</TD></TR><TR><TD>Q407</TD><TD>12,438</TD><TD>1/4/2022</TD></TR><TR><TD>Q239</TD><TD>589.480</TD><TD>1/4/2022</TD></TR><TR><TD>Q484</TD><TD>2,914.160</TD><TD>1/4/2022</TD></TR><TR><TD>Q958</TD><TD>4,012.240</TD><TD>1/4/2022</TD></TR><TR><TD>A354</TD><TD>29,700</TD><TD>1/4/2022</TD></TR><TR><TD>L160</TD><TD>52</TD><TD>1/4/2022</TD></TR><TR><TD>L350</TD><TD>2</TD><TD>1/4/2022</TD></TR><TR><TD>L314</TD><TD>27</TD><TD>1/4/2022</TD></TR><TR><TD>L314</TD><TD>33</TD><TD>1/4/2022</TD></TR><TR><TD>L900</TD><TD>5</TD><TD>1/4/2022</TD></TR><TR><TD>L640</TD><TD>4</TD><TD>1/4/2022</TD></TR><TR><TD>L630</TD><TD>63</TD><TD>1/4/2022</TD></TR><TR><TD>L480</TD><TD>89</TD><TD>1/4/2022</TD></TR><TR><TD>L190</TD><TD>81</TD><TD>1/4/2022</TD></TR><TR><TD>L200</TD><TD>54</TD><TD>1/4/2022</TD></TR><TR><TD>L350</TD><TD>27</TD><TD>1/4/2022</TD></TR><TR><TD>L470</TD><TD>27</TD><TD>1/4/2022</TD></TR><TR><TD>B042</TD><TD>1,350</TD><TD>1/4/2022</TD></TR><TR><TD>L240</TD><TD>27</TD><TD>1/4/2022</TD></TR><TR><TD>L316</TD><TD>54</TD><TD>1/4/2022</TD></TR><TR><TD>L320</TD><TD>54</TD><TD>1/4/2022</TD></TR><TR><TD>L500</TD><TD>324</TD><TD>1/4/2022</TD></TR><TR><TD>L490</TD><TD>324</TD><TD>1/4/2022</TD></TR><TR><TD>L430</TD><TD>324</TD><TD>1/4/2022</TD></TR><TR><TD>L500</TD><TD>324</TD><TD>1/4/2022</TD></TR><TR><TD>L490</TD><TD>324</TD><TD>1/5/2022</TD></TR><TR><TD>L430</TD><TD>324</TD><TD>1/5/2022</TD></TR><TR><TD>L160</TD><TD>432</TD><TD>1/5/2022</TD></TR><TR><TD>L350</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L314</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L314</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L900</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L640</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L630</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L480</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L190</TD><TD>81</TD><TD>1/5/2022</TD></TR><TR><TD>L200</TD><TD>54</TD><TD>1/5/2022</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P> Wed, 30 Nov 2022 15:01:19 GMT Anonymous 2022-11-30T15:01:19Z Scrap Ratio (Dax formula that uses data from two different tables) https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Scrap-Ratio-Dax-formula-that-uses-data-from-two-different-tables/m-p/2931634#M96454 <P>Hello,</P><P>&nbsp;</P><P>I could really use some help. I want to write a DAX formula to determine each part's scrap ratio. However,&nbsp;I cannot figure out why it is not calculating. I have a list of labels made in one table and a list of parts scrapped in another. I only want to count the part numbers that are being discarded. Can anyone help?</P><P>&nbsp;</P><P>Labels Made</P><TABLE border="1"><TBODY><TR><TD>Part Number</TD><TD>Quantity&nbsp;</TD></TR><TR><TD>A</TD><TD>4</TD></TR><TR><TD>B</TD><TD>4</TD></TR><TR><TD>C</TD><TD>5</TD></TR><TR><TD>C</TD><TD>4</TD></TR><TR><TD>B</TD><TD>5</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Parts Scrapped</P><TABLE border="1"><TBODY><TR><TD>Parts</TD><TD>Quantity</TD></TR><TR><TD>A</TD><TD>1</TD></TR><TR><TD>B</TD><TD>1</TD></TR><TR><TD>B</TD><TD>2</TD></TR><TR><TD>A</TD><TD>1</TD></TR><TR><TD>A</TD><TD>1</TD></TR></TBODY></TABLE> Mon, 28 Nov 2022 02:52:40 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Scrap-Ratio-Dax-formula-that-uses-data-from-two-different-tables/m-p/2931634#M96454 Anonymous 2022-11-28T02:52:40Z Re: Scrap Ratio (Dax formula that uses data from two different tables) https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Scrap-Ratio-Dax-formula-that-uses-data-from-two-different-tables/m-p/2931709#M96457 <P>what do you mean by discarded?</P> Mon, 28 Nov 2022 03:24:16 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Scrap-Ratio-Dax-formula-that-uses-data-from-two-different-tables/m-p/2931709#M96457 FreemanZ 2022-11-28T03:24:16Z Re: Scrap Ratio (Dax formula that uses data from two different tables) https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Scrap-Ratio-Dax-formula-that-uses-data-from-two-different-tables/m-p/2931722#M96458 <P>Anonymous</LI-USER> , Create a common Parts Table and then create a measure </P> <P>&nbsp;</P> <P>divide(Sum(Scrapped[Quantity]), Sum(Labels [Quantity]))</P> <P>&nbsp;</P> <P>&nbsp;</P> <P>Analyze with common table&nbsp;</P> <P>Parts = distinct(union(distinct(Table1[Parts]),distinct(Table2[Parts])))</P> <P>&nbsp;</P> <P><A href="https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bda" target="_blank">https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bda</A></P> <P>&nbsp;</P> <P>&nbsp;</P> Mon, 28 Nov 2022 03:28:03 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Scrap-Ratio-Dax-formula-that-uses-data-from-two-different-tables/m-p/2931722#M96458 amitchandak 2022-11-28T03:28:03Z Re: Scrap Ratio (Dax formula that uses data from two different tables) https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Scrap-Ratio-Dax-formula-that-uses-data-from-two-different-tables/m-p/2939289#M97019 <P>I need the information to stay on the table. Power Bi will sum the scrap, but it is not summering my labels made. I can only assume it is because of the mass quantity of data, but that does not make sense. I tried clearing all filters, but that did not fix the problem. I included a little more detail about my problem.</P><P>&nbsp;</P><P>Scrap Made</P><TABLE><TBODY><TR><TD>Material</TD><TD>Quantity</TD><TD>Scrapping date</TD></TR><TR><TD>Q339</TD><TD>0.600</TD><TD>11/4/2022</TD></TR><TR><TD>Q966</TD><TD>-88.540</TD><TD>11/4/2022</TD></TR><TR><TD>Q231</TD><TD>-115</TD><TD>11/4/2022</TD></TR><TR><TD>Q545</TD><TD>-22.480</TD><TD>11/4/2022</TD></TR><TR><TD>S318</TD><TD>809</TD><TD>11/4/2022</TD></TR><TR><TD>Q407</TD><TD>-4,146</TD><TD>11/4/2022</TD></TR><TR><TD>Q239</TD><TD>-117.896</TD><TD>11/4/2022</TD></TR><TR><TD>Q484</TD><TD>-1,457.080</TD><TD>11/4/2022</TD></TR><TR><TD>Q958</TD><TD>-501.530</TD><TD>11/4/2022</TD></TR><TR><TD>A354</TD><TD>-450</TD><TD>11/4/2022</TD></TR><TR><TD>L160</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L350</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L314</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L314</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L900</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L640</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L630</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L480</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L190</TD><TD>3</TD><TD>11/4/2022</TD></TR><TR><TD>L200</TD><TD>2</TD><TD>11/4/2022</TD></TR><TR><TD>L350</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L470</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>B042</TD><TD>50</TD><TD>11/4/2022</TD></TR><TR><TD>L240</TD><TD>1</TD><TD>11/4/2022</TD></TR><TR><TD>L316</TD><TD>2</TD><TD>11/4/2022</TD></TR><TR><TD>L320</TD><TD>2</TD><TD>11/4/2022</TD></TR><TR><TD>L500</TD><TD>12</TD><TD>11/4/2022</TD></TR><TR><TD>L490</TD><TD>12</TD><TD>11/4/2022</TD></TR><TR><TD>L430</TD><TD>12</TD><TD>11/4/2022</TD></TR><TR><TD>L500</TD><TD>12</TD><TD>11/4/2022</TD></TR><TR><TD>L490</TD><TD>12</TD><TD>11/5/2022</TD></TR><TR><TD>L430</TD><TD>12</TD><TD>11/5/2022</TD></TR><TR><TD>L160</TD><TD>16</TD><TD>11/5/2022</TD></TR><TR><TD>L350</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L314</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L314</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L900</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L640</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L630</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L480</TD><TD>1</TD><TD>11/5/2022</TD></TR><TR><TD>L190</TD><TD>3</TD><TD>11/5/2022</TD></TR><TR><TD>L200</TD><TD>2</TD><TD>11/5/2022</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>Labels Made</P><TABLE><TBODY><TR><TD>Material</TD><TD>Quantity</TD><TD>Scrapping date</TD></TR><TR><TD>Q339</TD><TD>0.600</TD><TD>1/4/2022</TD></TR><TR><TD>Q966</TD><TD>796.860</TD><TD>1/4/2022</TD></TR><TR><TD>Q231</TD><TD>345</TD><TD>1/4/2022</TD></TR><TR><TD>Q545</TD><TD>899.200</TD><TD>1/4/2022</TD></TR><TR><TD>S318</TD><TD>809</TD><TD>1/4/2022</TD></TR><TR><TD>Q407</TD><TD>12,438</TD><TD>1/4/2022</TD></TR><TR><TD>Q239</TD><TD>589.480</TD><TD>1/4/2022</TD></TR><TR><TD>Q484</TD><TD>2,914.160</TD><TD>1/4/2022</TD></TR><TR><TD>Q958</TD><TD>4,012.240</TD><TD>1/4/2022</TD></TR><TR><TD>A354</TD><TD>29,700</TD><TD>1/4/2022</TD></TR><TR><TD>L160</TD><TD>52</TD><TD>1/4/2022</TD></TR><TR><TD>L350</TD><TD>2</TD><TD>1/4/2022</TD></TR><TR><TD>L314</TD><TD>27</TD><TD>1/4/2022</TD></TR><TR><TD>L314</TD><TD>33</TD><TD>1/4/2022</TD></TR><TR><TD>L900</TD><TD>5</TD><TD>1/4/2022</TD></TR><TR><TD>L640</TD><TD>4</TD><TD>1/4/2022</TD></TR><TR><TD>L630</TD><TD>63</TD><TD>1/4/2022</TD></TR><TR><TD>L480</TD><TD>89</TD><TD>1/4/2022</TD></TR><TR><TD>L190</TD><TD>81</TD><TD>1/4/2022</TD></TR><TR><TD>L200</TD><TD>54</TD><TD>1/4/2022</TD></TR><TR><TD>L350</TD><TD>27</TD><TD>1/4/2022</TD></TR><TR><TD>L470</TD><TD>27</TD><TD>1/4/2022</TD></TR><TR><TD>B042</TD><TD>1,350</TD><TD>1/4/2022</TD></TR><TR><TD>L240</TD><TD>27</TD><TD>1/4/2022</TD></TR><TR><TD>L316</TD><TD>54</TD><TD>1/4/2022</TD></TR><TR><TD>L320</TD><TD>54</TD><TD>1/4/2022</TD></TR><TR><TD>L500</TD><TD>324</TD><TD>1/4/2022</TD></TR><TR><TD>L490</TD><TD>324</TD><TD>1/4/2022</TD></TR><TR><TD>L430</TD><TD>324</TD><TD>1/4/2022</TD></TR><TR><TD>L500</TD><TD>324</TD><TD>1/4/2022</TD></TR><TR><TD>L490</TD><TD>324</TD><TD>1/5/2022</TD></TR><TR><TD>L430</TD><TD>324</TD><TD>1/5/2022</TD></TR><TR><TD>L160</TD><TD>432</TD><TD>1/5/2022</TD></TR><TR><TD>L350</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L314</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L314</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L900</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L640</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L630</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L480</TD><TD>27</TD><TD>1/5/2022</TD></TR><TR><TD>L190</TD><TD>81</TD><TD>1/5/2022</TD></TR><TR><TD>L200</TD><TD>54</TD><TD>1/5/2022</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P> Wed, 30 Nov 2022 15:01:19 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Scrap-Ratio-Dax-formula-that-uses-data-from-two-different-tables/m-p/2939289#M97019 Anonymous 2022-11-30T15:01:19Z Re: Scrap Ratio (Dax formula that uses data from two different tables) https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Scrap-Ratio-Dax-formula-that-uses-data-from-two-different-tables/m-p/2939297#M97020 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="460868" data-lia-user-login="FreemanZ" class="lia-mention lia-mention-user">FreemanZ</a>,&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>Sorry about that, discarded material = scrap material.</P> Fri, 02 Dec 2022 15:08:23 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Scrap-Ratio-Dax-formula-that-uses-data-from-two-different-tables/m-p/2939297#M97020 Anonymous 2022-12-02T15:08:23Z