topic Re: SUMX filter (value smaller 1) does not find correct results in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/SUMX-filter-value-smaller-1-does-not-find-correct-results/m-p/2089989#M47568
<P>see attached. You didn't mention if you wanted working totals.</P>Wed, 22 Sep 2021 01:36:49 GMTlbendlin2021-09-22T01:36:49ZSUMX filter (value smaller 1) does not find correct results
https://community.powerbi.com/t5/DAX-Commands-and-Tips/SUMX-filter-value-smaller-1-does-not-find-correct-results/m-p/2086179#M47426
<P>Hello,</P><P>After trying many different formul and search terms I cannot find a solution for the following problem.<BR /><BR />I've got a fact table with sales by customer and product. However, it could happen that products are given for free. In this case it should calculate the missed sales (free of charge) by multiplying the quantity with the reference price.<BR /><BR />Fact table:</P><TABLE><TBODY><TR><TD>Invoice No.</TD><TD>Customer</TD><TD>Material </TD><TD>Quantity</TD><TD>Sales price per piece</TD><TD>Total sales price</TD><TD>Reference price per piece</TD></TR><TR><TD>1</TD><TD>Alpha</TD><TD>A</TD><TD>4</TD><TD>10</TD><TD>40</TD><TD>10</TD></TR><TR><TD>2</TD><TD>Beta</TD><TD>A</TD><TD>2</TD><TD>0</TD><TD>0</TD><TD>10</TD></TR><TR><TD>3</TD><TD>Gamma</TD><TD>A</TD><TD>10</TD><TD>10</TD><TD>100</TD><TD>10</TD></TR><TR><TD>4</TD><TD>Gamma</TD><TD>B</TD><TD>8</TD><TD>0</TD><TD>0</TD><TD>5</TD></TR></TBODY></TABLE><P> </P><P>Desired output:</P><TABLE><TBODY><TR><TD>Customer</TD><TD>Total sales</TD><TD>Free of charge</TD></TR><TR><TD>Alpha</TD><TD>40</TD><TD>0</TD><TD> </TD></TR><TR><TD>Beta</TD><TD>0</TD><TD>20</TD><TD>(2*10)</TD></TR><TR><TD>Gamma</TD><TD>100</TD><TD>40</TD><TD>(8*5)</TD></TR></TBODY></TABLE><P> </P><P>My idea which is not working (result on lowest level as well as on aggregated level is wrong):</P><P> </P><P>Missed sales = SUMX(</P><P class="lia-indent-padding-left-60px">FILTER ( Fact_Table; Sales price per piece < 1) ;</P><P class="lia-indent-padding-left-60px">Quantity * Reference price per piece</P><P class="lia-indent-padding-left-60px">)</P><P class="lia-indent-padding-left-60px"> </P><P>(smaller 1 because some products are not given at zero but for some cents)<BR /><BR /></P><P> </P><P> </P><P>Thanks for any hints and suggestions in advance!</P>Mon, 20 Sep 2021 11:42:19 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/SUMX-filter-value-smaller-1-does-not-find-correct-results/m-p/2086179#M47426RonSon20152021-09-20T11:42:19ZRe: SUMX filter (value smaller 1) does not find correct results
https://community.powerbi.com/t5/DAX-Commands-and-Tips/SUMX-filter-value-smaller-1-does-not-find-correct-results/m-p/2089989#M47568
<P>see attached. You didn't mention if you wanted working totals.</P>Wed, 22 Sep 2021 01:36:49 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/SUMX-filter-value-smaller-1-does-not-find-correct-results/m-p/2089989#M47568lbendlin2021-09-22T01:36:49ZRe: SUMX filter (value smaller 1) does not find correct results
https://community.powerbi.com/t5/DAX-Commands-and-Tips/SUMX-filter-value-smaller-1-does-not-find-correct-results/m-p/2095220#M47753
<P>Thank you for your effort to create that example in PowerBI. It's a solution but I also need working totals to answer the question: What is the total value of freebies and then to analyze to what customer and what products were given for free.</P>Thu, 23 Sep 2021 18:44:42 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/SUMX-filter-value-smaller-1-does-not-find-correct-results/m-p/2095220#M47753RonSon20152021-09-23T18:44:42ZRe: SUMX filter (value smaller 1) does not find correct results
https://community.powerbi.com/t5/DAX-Commands-and-Tips/SUMX-filter-value-smaller-1-does-not-find-correct-results/m-p/2095242#M47756
<P>Here's a modified measure</P>
<P> </P>
<LI-CODE lang="markup">free of charge 2 =
var c=SUMMARIZE('Table','Table'[Customer],"free",sumx('Table',if('Table'[Sales price per piece]=0,'Table'[Quantity]*'Table'[Reference price per piece],0)))
return sumx(c,[free])</LI-CODE>
<P> </P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="lbendlin_0-1632423776344.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/599795iDB688C137BAFE519/image-size/medium?v=v2&px=400" role="button" title="lbendlin_0-1632423776344.png" alt="lbendlin_0-1632423776344.png" /></span></P>
<P> </P>Thu, 23 Sep 2021 19:03:04 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/SUMX-filter-value-smaller-1-does-not-find-correct-results/m-p/2095242#M47756lbendlin2021-09-23T19:03:04Z