topic Re: How to get SUMX Total when using iterator in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-SUMX-Total-when-using-iterator/m-p/1831690#M38964 <P>Hi,&nbsp;Anonymous</LI-USER>&nbsp;</P><P>If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measrue.</P><P>Thanks.</P> Sun, 09 May 2021 02:50:38 GMT Jihwan_Kim 2021-05-09T02:50:38Z How to get SUMX Total when using iterator https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-SUMX-Total-when-using-iterator/m-p/1831500#M38956 <P>Hello</P><P>&nbsp;</P><P>I'm counting the number of new customers in a measure by year iterating through each Customer ID. The 1 in Number of New Customers indicates that it's a new customer. I'm using SUMX, how can I get the total?</P><P>&nbsp;</P><P>Here's my code:</P><DIV><DIV><SPAN>CALCULATE(</SPAN></DIV><DIV><SPAN>SUMX(</SPAN></DIV><DIV><SPAN>VALUES(Customer[Customer ID]),</SPAN></DIV><DIV><SPAN>DISTINCTCOUNT(Sales[Customer ID)</SPAN></DIV><DIV><SPAN>),</SPAN></DIV><DIV><SPAN>FILTER(</SPAN></DIV><DIV><SPAN>'Customer',Cust1stSalesDate &gt;= FromSalesDate &amp;&amp; </SPAN></DIV><DIV><SPAN>Cust1stSalesDate &lt;= ToSalesDate</SPAN></DIV><DIV><SPAN>) </SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN><SPAN><img /></SPAN></SPAN><P>&nbsp;</P></DIV></DIV><P><BR /><BR /></P> Sat, 08 May 2021 18:08:51 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-SUMX-Total-when-using-iterator/m-p/1831500#M38956 Anonymous 2021-05-08T18:08:51Z Re: How to get SUMX Total when using iterator https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-SUMX-Total-when-using-iterator/m-p/1831690#M38964 <P>Hi,&nbsp;Anonymous</LI-USER>&nbsp;</P><P>If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measrue.</P><P>Thanks.</P> Sun, 09 May 2021 02:50:38 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-SUMX-Total-when-using-iterator/m-p/1831690#M38964 Jihwan_Kim 2021-05-09T02:50:38Z Re: How to get SUMX Total when using iterator https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-SUMX-Total-when-using-iterator/m-p/1831701#M38965 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="291628" data-lia-user-login="Jihwan_Kim" class="lia-mention lia-mention-user">Jihwan_Kim</a>&nbsp;<BR />Thank you for replying, I have attached a sample file of what I'm trying to do.&nbsp;</P><P><A href="https://www.dropbox.com/s/2j2y0twez8iwc6d/lefli_sumx.pbix?dl=0" target="_self">Sample File</A>&nbsp;<BR />Please note that it has to be a measure and not a calculated column.&nbsp;<BR /><BR /></P> Sun, 09 May 2021 03:48:39 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-SUMX-Total-when-using-iterator/m-p/1831701#M38965 Anonymous 2021-05-09T03:48:39Z Re: How to get SUMX Total when using iterator https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-SUMX-Total-when-using-iterator/m-p/1831707#M38966 <P>Hi,&nbsp;Anonymous</a>&nbsp;</P><P>Thank you for sharing.</P><P>Please check the below picture and the link.</P><P>&nbsp;</P><P>&nbsp;</P><DIV><DIV><STRONG><EM>Number of new customers total fix = </EM></STRONG></DIV><DIV><EM>SUMX( VALUES( data[Customer ID]), [Number of new customers])</EM></DIV><DIV><EM>&nbsp;</EM></DIV><DIV><DIV><DIV><STRONG><EM>Number of new customers New version = </EM></STRONG></DIV><DIV><EM>VAR FromSalesDate =</EM></DIV><DIV><EM>FIRSTDATE ( DimDates[Date] )</EM></DIV><DIV><EM>VAR ToSalesDate =</EM></DIV><DIV><EM>LASTDATE ( DimDates[Date] )</EM></DIV><DIV><EM>RETURN</EM></DIV><DIV><EM>COALESCE (</EM></DIV><DIV><EM>SUMX (</EM></DIV><DIV><EM>VALUES ( data[Customer ID] ),</EM></DIV><DIV><EM>CALCULATE (</EM></DIV><DIV><EM>COUNTROWS (</EM></DIV><DIV><EM>FILTER (</EM></DIV><DIV><EM>VALUES ( data[Customer ID] ),</EM></DIV><DIV><EM>MIN ( data[Customer First Sales Date] ) &gt;= FromSalesDate</EM></DIV><DIV><EM>&amp;&amp; MIN ( data[Customer First Sales Date] ) &lt;= ToSalesDate</EM></DIV><DIV><EM>)</EM></DIV><DIV><EM>)</EM></DIV><DIV><EM>)</EM></DIV><DIV><EM>),</EM></DIV><DIV><EM>0</EM></DIV><DIV><EM>)</EM></DIV><DIV><EM>&nbsp;</EM></DIV><DIV><SPAN><img /></SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN><A href="https://www.dropbox.com/s/7ha3qx430og5tme/lefli_sumx.pbix?dl=0" target="_self">https://www.dropbox.com/s/7ha3qx430og5tme/lefli_sumx.pbix?dl=0</A>&nbsp;</SPAN></DIV><DIV>&nbsp;</DIV><DIV>&nbsp;</DIV><DIV><P><FONT face="”calibri" color="”#0000FF”"><STRONG>Hi, My name is Jihwan Kim. </STRONG></FONT></P><SPAN><SPAN><BR /></SPAN></SPAN><P><FONT face="”calibri" color="”#0000FF”"><STRONG>If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up. </STRONG></FONT></P><SPAN><SPAN><BR /></SPAN></SPAN><P><FONT face="”calibri" color="”#0000FF”">Linkedin: linkedin.com/in/jihwankim1975/</FONT></P><P><FONT face="”calibri" color="”#0000FF”">Twitter: twitter.com/Jihwan_JHKIM</FONT></P></DIV></DIV></DIV></DIV> Sun, 09 May 2021 04:21:16 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-SUMX-Total-when-using-iterator/m-p/1831707#M38966 Jihwan_Kim 2021-05-09T04:21:16Z Re: How to get SUMX Total when using iterator https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-SUMX-Total-when-using-iterator/m-p/1831724#M38967 <P>Thank you that worked!&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="291628" data-lia-user-login="Jihwan_Kim" class="lia-mention lia-mention-user">Jihwan_Kim</a>&nbsp;</P> Sun, 09 May 2021 05:02:08 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-SUMX-Total-when-using-iterator/m-p/1831724#M38967 Anonymous 2021-05-09T05:02:08Z