topic How to calculate average profit ONLY when the product has been bought and sold in selected time. in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-average-profit-ONLY-when-the-product-has-been/m-p/3556564#M136923 <P>Hello.&nbsp;</P><P>&nbsp;</P><P>I need to calculate the average profit of per product and in general in the selected time period. It is for a scrap business that buys scrap and then sells it forward. However, I only want to calculate profits for a product that has been bought AND sold in the selected period, because otherwise it will calculate a loss if a product has been recently bought in the selected time period but not sold. Now I have created a DAX formula that can calculate this on single products. Formula looks like this:&nbsp;</P><DIV><DIV><SPAN>Kate keskimäärin =</SPAN></DIV><DIV><SPAN>IF</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>NOT</SPAN><SPAN>(</SPAN><SPAN>ISBLANK</SPAN><SPAN>(</SPAN><SPAN>[Ostot keskimäärin €/T]</SPAN><SPAN>)) &amp;&amp; </SPAN><SPAN>NOT</SPAN><SPAN>(</SPAN><SPAN>ISBLANK</SPAN><SPAN>(</SPAN><SPAN>[Myynti keskimäärin €/T]</SPAN><SPAN>)),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>[Myynti keskimäärin €/T]</SPAN><SPAN> - </SPAN><SPAN>Ostot</SPAN><SPAN>[Ostot keskimäärin €/T]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>BLANK</SPAN><SPAN>()</SPAN></DIV><DIV><SPAN>)<BR /></SPAN>Where "<SPAN>Ostot keskimäärin €/T" is average buying price and "Myynti keskimäärin €/T" is average sales price. It works and calculates this on single products but when it comes to the overall average on all the products it calculates also the ones that how been bought but not sold and vice versa.&nbsp;<BR />&nbsp;<img /><BR />As you can see in this table the total is calculated falsley since it includes everything. Does anybody know how I can get the average profit in total? It doesnt necessarily have to be visible in the table but I need the calculation to show the average profit All help will be appriciated.&nbsp;</SPAN></DIV></DIV> Tue, 28 Nov 2023 13:46:50 GMT dblw 2023-11-28T13:46:50Z How to calculate average profit ONLY when the product has been bought and sold in selected time. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-average-profit-ONLY-when-the-product-has-been/m-p/3556564#M136923 <P>Hello.&nbsp;</P><P>&nbsp;</P><P>I need to calculate the average profit of per product and in general in the selected time period. It is for a scrap business that buys scrap and then sells it forward. However, I only want to calculate profits for a product that has been bought AND sold in the selected period, because otherwise it will calculate a loss if a product has been recently bought in the selected time period but not sold. Now I have created a DAX formula that can calculate this on single products. Formula looks like this:&nbsp;</P><DIV><DIV><SPAN>Kate keskimäärin =</SPAN></DIV><DIV><SPAN>IF</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>NOT</SPAN><SPAN>(</SPAN><SPAN>ISBLANK</SPAN><SPAN>(</SPAN><SPAN>[Ostot keskimäärin €/T]</SPAN><SPAN>)) &amp;&amp; </SPAN><SPAN>NOT</SPAN><SPAN>(</SPAN><SPAN>ISBLANK</SPAN><SPAN>(</SPAN><SPAN>[Myynti keskimäärin €/T]</SPAN><SPAN>)),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>[Myynti keskimäärin €/T]</SPAN><SPAN> - </SPAN><SPAN>Ostot</SPAN><SPAN>[Ostot keskimäärin €/T]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>BLANK</SPAN><SPAN>()</SPAN></DIV><DIV><SPAN>)<BR /></SPAN>Where "<SPAN>Ostot keskimäärin €/T" is average buying price and "Myynti keskimäärin €/T" is average sales price. It works and calculates this on single products but when it comes to the overall average on all the products it calculates also the ones that how been bought but not sold and vice versa.&nbsp;<BR />&nbsp;<img /><BR />As you can see in this table the total is calculated falsley since it includes everything. Does anybody know how I can get the average profit in total? It doesnt necessarily have to be visible in the table but I need the calculation to show the average profit All help will be appriciated.&nbsp;</SPAN></DIV></DIV> Tue, 28 Nov 2023 13:46:50 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-average-profit-ONLY-when-the-product-has-been/m-p/3556564#M136923 dblw 2023-11-28T13:46:50Z Re: How to calculate average profit ONLY when the product has been bought and sold in selected time. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-average-profit-ONLY-when-the-product-has-been/m-p/3556577#M136926 <P>hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="563959" data-lia-user-login="dblw" class="lia-mention lia-mention-user">dblw</a>&nbsp;,</P> <P>&nbsp;</P> <P>try like:</P> <DIV>Profit =</DIV> <DIV>AVERAGEX(</DIV> <DIV><SPAN>&nbsp; &nbsp; FILTER(</SPAN></DIV> <DIV><SPAN>&nbsp;&nbsp; &nbsp;&nbsp; &nbsp; tablename,</SPAN></DIV> <DIV><SPAN>&nbsp;&nbsp; &nbsp;</SPAN><SPAN>&nbsp; &nbsp; NOT ISBLANK(</SPAN>[buyingprice])</DIV> <DIV><SPAN>&nbsp;&nbsp; &nbsp;</SPAN><SPAN>&nbsp;&nbsp; &nbsp;</SPAN><SPAN>&nbsp; &nbsp; &amp;&amp;</SPAN>NOT ISBLANK([sellingprice])</DIV> <DIV><SPAN>&nbsp; &nbsp; ),</SPAN></DIV> <DIV><SPAN>&nbsp; &nbsp; [</SPAN>sellingprice<SPAN>] - [</SPAN>buyingprice<SPAN>]</SPAN></DIV> <DIV>)</DIV> <P><LI-WRAPPER> </LI-WRAPPER></P> Tue, 28 Nov 2023 13:53:51 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-average-profit-ONLY-when-the-product-has-been/m-p/3556577#M136926 FreemanZ 2023-11-28T13:53:51Z Re: How to calculate average profit ONLY when the product has been bought and sold in selected time. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-average-profit-ONLY-when-the-product-has-been/m-p/3556595#M136928 <P>This worked. Thank you!</P> Tue, 28 Nov 2023 14:01:15 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-average-profit-ONLY-when-the-product-has-been/m-p/3556595#M136928 dblw 2023-11-28T14:01:15Z