topic Re: Measure for Monthly Sum with Filter in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-for-Monthly-Sum-with-Filter/m-p/4112364#M163199 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="588039" data-lia-user-login="vkisa" class="lia-mention lia-mention-user">vkisa</a>&nbsp;,</P> <P>Regarding your question,&nbsp;Is it because the sales volume is 0 that excludes the calculation of '1001'?</P> <P><img /></P> <P>Try this.</P> <LI-CODE lang="markup">MEASURE = VAR _table = ADDCOLUMNS ( SUMMARIZE ( 'Table1', [Customerid], "SalesA", SUM ( Table1[SalesAmountA] ), "SalesB", SUM ( Table1[SalesAmountB] ), "Deposit", SUM ( Table1[Deposit] ) ), "isAccomplish", IF ( [SalesA] &lt;&gt; 0 &amp;&amp; [SalesB] &lt;&gt; 0, TRUE (), FALSE () ) ) RETURN SUMX ( FILTER ( _table, [isAccomplish] = TRUE () ), [Deposit] )</LI-CODE> <P><img /></P> <P>Best Regards,<BR />Wenbin Zhou</P> Thu, 22 Aug 2024 02:34:26 GMT Anonymous 2024-08-22T02:34:26Z Measure for Monthly Sum with Filter https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-for-Monthly-Sum-with-Filter/m-p/4111659#M163174 <P>Hi,</P><P>I need some help to get measure that calculates the <U>total deposit for specific customers that had accomplished sales both product A and B (green cells).</U></P><P>I don't want to create a new calculated monthly table (Temporary Table).</P><P>&nbsp;</P><P>I want to get&nbsp;<FONT color="#000000"><STRONG>TotalDeposit(MultipleSales) </STRONG>column in my result table.</FONT></P><P>&nbsp;</P><P>Data Table-Table1</P><TABLE border="1"><TBODY><TR><TD>Year</TD><TD>Month</TD><TD>Customerid</TD><TD>SalesAmountA</TD><TD>SalesAmountB</TD><TD>Deposit</TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#993366">1000</FONT></TD><TD><FONT color="#993366">50</FONT></TD><TD><FONT color="#993366">0</FONT></TD><TD><FONT color="#993366">10</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#993366">1000</FONT></TD><TD><FONT color="#993366">30</FONT></TD><TD><FONT color="#993366">20</FONT></TD><TD><FONT color="#993366">10</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#993366">1000</FONT></TD><TD><FONT color="#993366">10</FONT></TD><TD><FONT color="#993366">10</FONT></TD><TD><FONT color="#993366">15</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#993366">1000</FONT></TD><TD><FONT color="#993366">0</FONT></TD><TD><FONT color="#993366">0</FONT></TD><TD><FONT color="#993366">0</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#993366">1000</FONT></TD><TD><FONT color="#993366">20</FONT></TD><TD><FONT color="#993366">20</FONT></TD><TD><FONT color="#993366">0</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#00FFFF">1001</FONT></TD><TD><FONT color="#00FFFF">0</FONT></TD><TD><FONT color="#00FFFF">10</FONT></TD><TD><FONT color="#00FFFF">5</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#00FFFF">1001</FONT></TD><TD><FONT color="#00FFFF">0</FONT></TD><TD><FONT color="#00FFFF">0</FONT></TD><TD><FONT color="#00FFFF">5</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#00FFFF">1001</FONT></TD><TD><FONT color="#00FFFF">0</FONT></TD><TD><FONT color="#00FFFF">0</FONT></TD><TD><FONT color="#00FFFF">0</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#00FFFF">1001</FONT></TD><TD><FONT color="#00FFFF">0</FONT></TD><TD><FONT color="#00FFFF">0</FONT></TD><TD><FONT color="#00FFFF">10</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#FF9900">1002</FONT></TD><TD><FONT color="#FF9900">5</FONT></TD><TD><FONT color="#FF9900">0</FONT></TD><TD><FONT color="#FF9900">0</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#FF9900">1002</FONT></TD><TD><FONT color="#FF9900">5</FONT></TD><TD><FONT color="#FF9900">0</FONT></TD><TD><FONT color="#FF9900">20</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD><FONT color="#FF9900">1002</FONT></TD><TD><FONT color="#FF9900">5</FONT></TD><TD><FONT color="#FF9900">10</FONT></TD><TD><FONT color="#FF9900">10</FONT></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Temporary Table (I don't want to create this table, it is just to explain what I want to)</P><TABLE border="1"><TBODY><TR><TD>CustomerID</TD><TD>Year</TD><TD>Month</TD><TD>MonthlySalesA</TD><TD>MonthlySalesB</TD><TD>MonthlyDeposit</TD></TR><TR><TD>1000</TD><TD>2024</TD><TD>1</TD><TD>110</TD><TD>50</TD><TD>35</TD></TR><TR><TD>1001</TD><TD>2024</TD><TD>1</TD><TD>0</TD><TD>10</TD><TD>20</TD></TR><TR><TD>1002</TD><TD>2024</TD><TD>1</TD><TD>15</TD><TD>10</TD><TD>30</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Table2(my result table)</P><TABLE border="1"><TBODY><TR><TD>Year</TD><TD>Month</TD><TD>CountofCustomer(MultipleSales)</TD><TD>TotalDeposit</TD><TD><FONT color="#008000">TotalDeposit(MultipleSales)</FONT></TD></TR><TR><TD>2024</TD><TD>1</TD><TD>2</TD><TD>85</TD><TD><FONT color="#008000">"35+30=65"</FONT></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Thank you,</P><P>Veli</P> Wed, 21 Aug 2024 15:53:16 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-for-Monthly-Sum-with-Filter/m-p/4111659#M163174 vkisa 2024-08-21T15:53:16Z Re: Measure for Monthly Sum with Filter https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-for-Monthly-Sum-with-Filter/m-p/4112364#M163199 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="588039" data-lia-user-login="vkisa" class="lia-mention lia-mention-user">vkisa</a>&nbsp;,</P> <P>Regarding your question,&nbsp;Is it because the sales volume is 0 that excludes the calculation of '1001'?</P> <P><img /></P> <P>Try this.</P> <LI-CODE lang="markup">MEASURE = VAR _table = ADDCOLUMNS ( SUMMARIZE ( 'Table1', [Customerid], "SalesA", SUM ( Table1[SalesAmountA] ), "SalesB", SUM ( Table1[SalesAmountB] ), "Deposit", SUM ( Table1[Deposit] ) ), "isAccomplish", IF ( [SalesA] &lt;&gt; 0 &amp;&amp; [SalesB] &lt;&gt; 0, TRUE (), FALSE () ) ) RETURN SUMX ( FILTER ( _table, [isAccomplish] = TRUE () ), [Deposit] )</LI-CODE> <P><img /></P> <P>Best Regards,<BR />Wenbin Zhou</P> Thu, 22 Aug 2024 02:34:26 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-for-Monthly-Sum-with-Filter/m-p/4112364#M163199 Anonymous 2024-08-22T02:34:26Z Re: Measure for Monthly Sum with Filter https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-for-Monthly-Sum-with-Filter/m-p/4113224#M163239 <P>Thank you&nbsp;Anonymous</a>&nbsp;, it worked !</P> Thu, 22 Aug 2024 09:44:57 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-for-Monthly-Sum-with-Filter/m-p/4113224#M163239 vkisa 2024-08-22T09:44:57Z