topic Dax formula output breaks when filtering on multiple tables in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-formula-output-breaks-when-filtering-on-multiple-tables/m-p/3060997#M105756 <P>I am creating a new column to calculate the weighted average price for our products based on multiple filters.<BR /><BR />I am getting what looks like a proper output when using this DAX formula.&nbsp;<BR /><BR />2022 Price List Weighted average Dealer Price Column 2 = CALCULATE(DIVIDE(SUMX('quotedetail',[Dealer Price Per Unit]*[Quantity]),SUMX('quotedetail',[Quantity])),<BR />FILTER(ALLSELECTED('quote'), quote[Price List] = "2022 Price List"),<BR />FILTER(ALLSELECTED('quote'), quote[statuscodename] &lt;&gt; "Revised"))</P><P><img /></P><P>&nbsp;</P><P>But as soon as I add in one more filter from my Quotedetails table the amounts are all the same.<BR /><BR /></P><DIV><DIV><SPAN>2022 Price List Weighted average Dealer Price Column 2 = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>DIVIDE</SPAN><SPAN>(</SPAN><SPAN>SUMX</SPAN><SPAN>(</SPAN><SPAN>'quotedetail'</SPAN><SPAN>,</SPAN><SPAN>[Dealer Price Per Unit]</SPAN><SPAN>*</SPAN><SPAN>[Quantity]</SPAN><SPAN>),</SPAN><SPAN>SUMX</SPAN><SPAN>(</SPAN><SPAN>'quotedetail'</SPAN><SPAN>,</SPAN><SPAN>[Quantity]</SPAN><SPAN>)), </SPAN></DIV><DIV><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>ALLSELECTED</SPAN><SPAN>(</SPAN><SPAN>'quote'</SPAN><SPAN>), </SPAN><SPAN>quote</SPAN><SPAN>[Price List]</SPAN><SPAN> = </SPAN><SPAN>"2022 Price List"</SPAN><SPAN>), </SPAN></DIV><DIV><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>ALLSELECTED</SPAN><SPAN>(</SPAN><SPAN>'quote'</SPAN><SPAN>), </SPAN><SPAN>quote</SPAN><SPAN>[statuscodename]</SPAN><SPAN> &lt;&gt; </SPAN><SPAN>"Revised"</SPAN><SPAN>),</SPAN></DIV><DIV><STRONG><STRONG>FILTER(ALLSELECTED('quotedetail'), quotedetail[Option Upcharge?] = "No"))<BR /></STRONG></STRONG><img /><P>&nbsp;</P><P>Any help with this is greatly appreciated!</P></DIV></DIV><P>&nbsp;</P> Fri, 03 Feb 2023 19:49:23 GMT Lindino08 2023-02-03T19:49:23Z Dax formula output breaks when filtering on multiple tables https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-formula-output-breaks-when-filtering-on-multiple-tables/m-p/3060997#M105756 <P>I am creating a new column to calculate the weighted average price for our products based on multiple filters.<BR /><BR />I am getting what looks like a proper output when using this DAX formula.&nbsp;<BR /><BR />2022 Price List Weighted average Dealer Price Column 2 = CALCULATE(DIVIDE(SUMX('quotedetail',[Dealer Price Per Unit]*[Quantity]),SUMX('quotedetail',[Quantity])),<BR />FILTER(ALLSELECTED('quote'), quote[Price List] = "2022 Price List"),<BR />FILTER(ALLSELECTED('quote'), quote[statuscodename] &lt;&gt; "Revised"))</P><P><img /></P><P>&nbsp;</P><P>But as soon as I add in one more filter from my Quotedetails table the amounts are all the same.<BR /><BR /></P><DIV><DIV><SPAN>2022 Price List Weighted average Dealer Price Column 2 = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>DIVIDE</SPAN><SPAN>(</SPAN><SPAN>SUMX</SPAN><SPAN>(</SPAN><SPAN>'quotedetail'</SPAN><SPAN>,</SPAN><SPAN>[Dealer Price Per Unit]</SPAN><SPAN>*</SPAN><SPAN>[Quantity]</SPAN><SPAN>),</SPAN><SPAN>SUMX</SPAN><SPAN>(</SPAN><SPAN>'quotedetail'</SPAN><SPAN>,</SPAN><SPAN>[Quantity]</SPAN><SPAN>)), </SPAN></DIV><DIV><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>ALLSELECTED</SPAN><SPAN>(</SPAN><SPAN>'quote'</SPAN><SPAN>), </SPAN><SPAN>quote</SPAN><SPAN>[Price List]</SPAN><SPAN> = </SPAN><SPAN>"2022 Price List"</SPAN><SPAN>), </SPAN></DIV><DIV><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>ALLSELECTED</SPAN><SPAN>(</SPAN><SPAN>'quote'</SPAN><SPAN>), </SPAN><SPAN>quote</SPAN><SPAN>[statuscodename]</SPAN><SPAN> &lt;&gt; </SPAN><SPAN>"Revised"</SPAN><SPAN>),</SPAN></DIV><DIV><STRONG><STRONG>FILTER(ALLSELECTED('quotedetail'), quotedetail[Option Upcharge?] = "No"))<BR /></STRONG></STRONG><img /><P>&nbsp;</P><P>Any help with this is greatly appreciated!</P></DIV></DIV><P>&nbsp;</P> Fri, 03 Feb 2023 19:49:23 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-formula-output-breaks-when-filtering-on-multiple-tables/m-p/3060997#M105756 Lindino08 2023-02-03T19:49:23Z Re: Dax formula output breaks when filtering on multiple tables https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-formula-output-breaks-when-filtering-on-multiple-tables/m-p/3061287#M105781 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="420402" data-lia-user-login="Lindino08" class="lia-mention lia-mention-user">Lindino08</a> , Use allselected , if you want to ignore some filter. Try like </P> <P>&nbsp;</P> <P>2022 Price List Weighted average Dealer Price Column 2 = CALCULATE(DIVIDE(SUMX('quotedetail',[Dealer Price Per Unit]*[Quantity]),SUMX('quotedetail',[Quantity])),<BR />FILTER(ALLSELECTED('quote'), quote[Price List] = "2022 Price List"),<BR />FILTER(ALLSELECTED('quote'), quote[statuscodename] &lt;&gt; "Revised"),<BR />FILTER(('quotedetail'), quotedetail[Option Upcharge?] = "No"))</P> <P>&nbsp;</P> <P>or</P> <P>&nbsp;</P> <P><BR />2022 Price List Weighted average Dealer Price Column 2 = CALCULATE(DIVIDE(SUMX('quotedetail',[Dealer Price Per Unit]*[Quantity]),SUMX('quotedetail',[Quantity])),<BR />FILTER(('quote'), quote[Price List] = "2022 Price List" &amp;&amp; quote[statuscodename] &lt;&gt; "Revised"),<BR />FILTER(('quotedetail'), quotedetail[Option Upcharge?] = "No"))</P> Sat, 04 Feb 2023 02:23:10 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-formula-output-breaks-when-filtering-on-multiple-tables/m-p/3061287#M105781 amitchandak 2023-02-04T02:23:10Z