topic Re: Custom Bin Size to Sum Supplier Amount in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1151279#M17516
<P>Hello <LI-USER uid="219786"></LI-USER> </P><P> </P><P>I tried to do a calculated column with the switch true formula instead of a measure, but know the calculated column just gives me 1 response of "Greater Than 500,000". The measure still works with the 5 different bins, but obviously I was trying to get the distinct count of suppliers within each of the bins. Do you know what I might be messing up?</P><P> </P><P>Also, how can i slightly change the sum_invoice measure to be impacted by filters. If i understand the AllExcept takes all filters off. This creates a problem if I'd like to filter by date or category if there a way to get sum while also being changed by filters?</P><P> </P><P>Thank you again for the help.</P>Wed, 10 Jun 2020 18:16:26 GMTrgold252020-06-10T18:16:26ZCustom Bin Size to Sum Supplier Amount
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1149112#M17440
<P>Hello. I would like to create 5 bins: 0-999, 1000-4,999 , 5,000-49,999, 50,000-499,999, >500,000</P><P> </P><P>I would like to get the grand total of each individual supplier and then distinct count each supplier within each of the bins.</P><P> </P><P>Below is some example data: </P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Supplier</TD><TD>Business Unit</TD><TD>Category</TD><TD>Invoice Details</TD><TD>Invoice Amount</TD></TR><TR><TD>Austin</TD><TD>Second</TD><TD>solid</TD><TD>spacer</TD><TD>$555,500.00</TD></TR><TR><TD>Dallas</TD><TD>Fourth</TD><TD>solid</TD><TD>heat pump</TD><TD>$4,440.00</TD></TR><TR><TD>Delaware</TD><TD>Second</TD><TD>solid</TD><TD>pump</TD><TD>$20.00</TD></TR><TR><TD>Raleigh</TD><TD>Third</TD><TD>service</TD><TD>spacer</TD><TD>$500.00</TD></TR><TR><TD>Raleigh</TD><TD>Third</TD><TD>service</TD><TD>spacer</TD><TD>$4,400.00</TD></TR><TR><TD>Raleigh</TD><TD>Third</TD><TD>service</TD><TD>heat pump</TD><TD>$3,000.00</TD></TR><TR><TD>Chicago</TD><TD>Second</TD><TD>metal</TD><TD>plate</TD><TD>$555.00</TD></TR><TR><TD>Brooklyn</TD><TD>Second</TD><TD>metal</TD><TD>spacer</TD><TD>$450.00</TD></TR><TR><TD>Times Square</TD><TD>Fourth</TD><TD>metal</TD><TD>heat pump</TD><TD>$4,400.00</TD></TR><TR><TD>Washington D.C</TD><TD>Fourth</TD><TD>metal</TD><TD>pump</TD><TD>$500.00</TD></TR><TR><TD>Houston</TD><TD>First</TD><TD>metal</TD><TD>gasket</TD><TD>$3,000.00</TD></TR><TR><TD>Raleigh</TD><TD>Third</TD><TD>metal</TD><TD>plate</TD><TD>$300.00</TD></TR><TR><TD>Chicago</TD><TD>Second</TD><TD>metal</TD><TD>pump</TD><TD>$222.00</TD></TR><TR><TD>Brooklyn</TD><TD>Second</TD><TD>metal</TD><TD>spacer</TD><TD>$322.00</TD></TR><TR><TD>Dallas</TD><TD>First</TD><TD>metal</TD><TD>gloves</TD><TD>$450,000.00</TD></TR><TR><TD>Chicago</TD><TD>Third</TD><TD>metal</TD><TD>gloves</TD><TD>$4,000.00</TD></TR><TR><TD>Brooklyn</TD><TD>Second</TD><TD>metal</TD><TD>plate</TD><TD>$430,000.00</TD></TR><TR><TD>Times Square</TD><TD>Fourth</TD><TD>metal</TD><TD>spacer</TD><TD>$3,400.00</TD></TR><TR><TD>Washington D.C</TD><TD>First</TD><TD>metal</TD><TD>heat pump</TD><TD>$530.00</TD></TR><TR><TD>Washington D.C</TD><TD>Fourth</TD><TD>Machine</TD><TD>gloves</TD><TD>$200.00</TD></TR><TR><TD>Wyoming</TD><TD>Second</TD><TD>Machine</TD><TD>screw</TD><TD>$2,000.00</TD></TR><TR><TD>Washington D.C</TD><TD>Fourth</TD><TD>Machine</TD><TD>screw</TD><TD>$540.00</TD></TR><TR><TD>Houston</TD><TD>First</TD><TD>fabric</TD><TD>screw</TD><TD>$80,000.00</TD></TR><TR><TD>Raleigh</TD><TD>First</TD><TD>fabric</TD><TD>gasket</TD><TD>$200.00</TD></TR><TR><TD>Chicago</TD><TD>Second</TD><TD>fabric</TD><TD>screw</TD><TD>$5,600.00</TD></TR><TR><TD>Austin</TD><TD>Five</TD><TD>fabric</TD><TD>gasket</TD><TD>$332.00</TD></TR><TR><TD>Dallas</TD><TD>Third</TD><TD>fabric</TD><TD>gasket</TD><TD>$400.00</TD></TR><TR><TD>Chicago</TD><TD>Fourth</TD><TD>fabric</TD><TD>heat pump</TD><TD>$345.00</TD></TR><TR><TD>Houston</TD><TD>Third</TD><TD>fabric</TD><TD>gasket</TD><TD>$200.00</TD></TR><TR><TD>Raleigh</TD><TD>Five</TD><TD>fabric</TD><TD>gasket</TD><TD>$30,000.00</TD></TR><TR><TD>Chicago</TD><TD>Fourth</TD><TD>fabric</TD><TD>gloves</TD><TD>$54,000.00</TD></TR><TR><TD>Austin</TD><TD>Second</TD><TD>fabric</TD><TD>plate</TD><TD>$1,200.00</TD></TR><TR><TD>Dallas</TD><TD>Third</TD><TD>fabric</TD><TD>spacer</TD><TD>$340.00</TD></TR></TBODY></TABLE><P> </P><P>I would like to get it to this: </P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Row Labels</TD><TD>Sum of Invoice Amount</TD><TD>Bins</TD></TR><TR><TD>Austin</TD><TD><SPAN> </SPAN>$<SPAN> </SPAN>557,032.00</TD><TD>Greater than 500,000</TD></TR><TR><TD>Brooklyn</TD><TD><SPAN> </SPAN>$<SPAN> </SPAN>430,772.00</TD><TD>Between 50000 and 500000</TD></TR><TR><TD>Chicago</TD><TD><SPAN> </SPAN>$<SPAN> </SPAN>64,722.00</TD><TD>Between 50000 and 500000</TD></TR><TR><TD>Dallas</TD><TD><SPAN> </SPAN>$<SPAN> </SPAN>455,180.00</TD><TD>Between 50000 and 500000</TD></TR><TR><TD>Delaware</TD><TD><SPAN> </SPAN>$<SPAN> </SPAN>20.00</TD><TD>Less Than 1000</TD></TR><TR><TD>Houston</TD><TD><SPAN> </SPAN>$<SPAN> </SPAN>83,200.00</TD><TD>Between 50000 and 500000</TD></TR><TR><TD>Raleigh</TD><TD><SPAN> </SPAN>$<SPAN> </SPAN>38,400.00</TD><TD>Between 50000 and 500000</TD></TR><TR><TD>Times Square</TD><TD><SPAN> </SPAN>$<SPAN> </SPAN>7,800.00</TD><TD>Between 5000 and 50000</TD></TR><TR><TD>Washington D.C</TD><TD><SPAN> </SPAN>$<SPAN> </SPAN>1,770.00</TD><TD>Less than 5000</TD></TR><TR><TD>Wyoming</TD><TD><SPAN> </SPAN>$<SPAN> </SPAN>2,000.00</TD><TD>Less Than 5000</TD></TR><TR><TD><P> </P></TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P>I would greatly appreciate any help. Thank you.</P>Tue, 09 Jun 2020 15:47:16 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1149112#M17440rgold252020-06-09T15:47:16ZRe: Custom Bin Size to Sum Supplier Amount
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1149160#M17443
<P>Hi <LI-USER uid="166590"></LI-USER> ,</P><P> </P><P>Create 2 measures</P><P> </P><DIV><DIV><SPAN>Sum of Invoices = CALCULATE(SUM('Table'[Invoice Amount]),ALLEXCEPT('Table','Table'[Supplier]))</SPAN></DIV><DIV> </DIV><DIV><DIV><DIV><SPAN>Bin = </SPAN></DIV><DIV><SPAN>SWITCH(</SPAN></DIV><DIV><SPAN>TRUE(),</SPAN></DIV><DIV><SPAN>'Table'[Sum of Invoices] >= 500000,"Greater than 500,000",</SPAN></DIV><DIV><SPAN>'Table'[Sum of Invoices] > 50000 && 'Table'[Sum of Invoices] <=499999,"Between 50000 and 500000",</SPAN></DIV><DIV><SPAN>'Table'[Sum of Invoices] > 5000 && 'Table'[Sum of Invoices] <=49999,"Between 5000 and 50000",</SPAN></DIV><DIV><SPAN>'Table'[Sum of Invoices]> 1000 && 'Table'[Sum of Invoices] <=4999,"Between 1000 and 5000",</SPAN></DIV><DIV><SPAN>'Table'[Sum of Invoices] <= 999,"Between 0 and 1000"</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV> </DIV><DIV> </DIV><DIV><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="1.jpg" style="width: 693px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/290421i79E1A4D427A75FE0/image-size/large?v=1.0&px=999" title="1.jpg" alt="1.jpg" /></span></SPAN></DIV><DIV> </DIV><DIV><SPAN>Regards,<BR />Harsh Nathani<BR /><BR /><FONT color="#0000FF"><STRONG>Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)</STRONG></FONT></SPAN></DIV></DIV></DIV></DIV>Tue, 09 Jun 2020 16:17:13 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1149160#M17443harshnathani2020-06-09T16:17:13ZRe: Custom Bin Size to Sum Supplier Amount
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1149260#M17445
<P>Thank you for the help <LI-USER uid="219786"></LI-USER> . How can I make a barplot with the distinct count of suppliers from the bins measure(show the count of bins)? I would like to show the count of the amount of suppliers within the 5 individual bins. I'm having difficulty making a barplot with the bins measure. Currently, it will not let me place the measure in anything but the Tooltips section. Thanks again for the help. </P>Tue, 09 Jun 2020 17:45:29 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1149260#M17445rgold252020-06-09T17:45:29ZRe: Custom Bin Size to Sum Supplier Amount
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1149282#M17448
<P>Hi <LI-USER uid="166590"></LI-USER> ,</P><P> </P><P>Create a Calulated Column then.</P><P> </P><DIV><DIV><SPAN>BIN Name = </SPAN></DIV><BR /><DIV><SPAN>SWITCH(</SPAN></DIV><DIV><SPAN>TRUE(),</SPAN></DIV><DIV><SPAN>'Table6'[Sum of Invoices] >= 500000,"Greater than 500,000",</SPAN></DIV><DIV><SPAN>'Table6'[Sum of Invoices] > 50000 && 'Table6'[Sum of Invoices] <=499999,"Between 50000 and 500000",</SPAN></DIV><DIV><SPAN>'Table6'[Sum of Invoices] > 5000 && 'Table6'[Sum of Invoices] <=49999,"Between 5000 and 50000",</SPAN></DIV><DIV><SPAN>'Table6'[Sum of Invoices]> 1000 && 'Table6'[Sum of Invoices] <=4999,"Between 1000 and 5000",</SPAN></DIV><DIV><SPAN>'Table6'[Sum of Invoices] <= 999,"Between 0 and 1000")</SPAN></DIV><DIV> </DIV><DIV><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="1.jpg" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/290450i22CE12D67089C045/image-size/large?v=1.0&px=999" title="1.jpg" alt="1.jpg" /></span></SPAN></DIV><DIV> </DIV><DIV> </DIV><DIV><P>Regards,<BR />Harsh Nathani</P><P><FONT face="arial black,avant garde" color="#0000FF"><STRONG>Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)</STRONG></FONT></P></DIV></DIV>Tue, 09 Jun 2020 17:55:33 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1149282#M17448harshnathani2020-06-09T17:55:33ZRe: Custom Bin Size to Sum Supplier Amount
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1151279#M17516
<P>Hello <LI-USER uid="219786"></LI-USER> </P><P> </P><P>I tried to do a calculated column with the switch true formula instead of a measure, but know the calculated column just gives me 1 response of "Greater Than 500,000". The measure still works with the 5 different bins, but obviously I was trying to get the distinct count of suppliers within each of the bins. Do you know what I might be messing up?</P><P> </P><P>Also, how can i slightly change the sum_invoice measure to be impacted by filters. If i understand the AllExcept takes all filters off. This creates a problem if I'd like to filter by date or category if there a way to get sum while also being changed by filters?</P><P> </P><P>Thank you again for the help.</P>Wed, 10 Jun 2020 18:16:26 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1151279#M17516rgold252020-06-10T18:16:26ZRe: Custom Bin Size to Sum Supplier Amount
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1151895#M17544
<P>Hi <LI-USER uid="166590"></LI-USER> ,</P><P> </P><P>Not sure why you have been getting the same values in all you have been getting the same value.</P><P> </P><P>Link to the file:</P><P><A href="https://drive.google.com/file/d/1pkH0zXiTMMnIwqm_XqmYkki5LKR3QY9e/view?usp=sharing" target="_blank">https://drive.google.com/file/d/1pkH0zXiTMMnIwqm_XqmYkki5LKR3QY9e/view?usp=sharing</A></P><P> </P><P><STRONG>Calculated Column</STRONG></P><DIV><SPAN>BIN Name =</SPAN></DIV><P> </P><DIV><SPAN>SWITCH(</SPAN></DIV><DIV><SPAN>TRUE(),</SPAN></DIV><DIV><SPAN>'Table6'[Sum of Invoices] >= 500000,"Greater than 500,000",</SPAN></DIV><DIV><SPAN>'Table6'[Sum of Invoices] > 50000 && 'Table6'[Sum of Invoices] <=499999,"Between 50000 and 500000",</SPAN></DIV><DIV><SPAN>'Table6'[Sum of Invoices] > 5000 && 'Table6'[Sum of Invoices] <=49999,"Between 5000 and 50000",</SPAN></DIV><DIV><SPAN>'Table6'[Sum of Invoices]> 1000 && 'Table6'[Sum of Invoices] <=4999,"Between 1000 and 5000",</SPAN></DIV><DIV><SPAN>'Table6'[Sum of Invoices] <= 999,"Between 0 and 1000")</SPAN></DIV><DIV> </DIV><DIV><SPAN>Incase you want the count of the Suppliers you can just pull the Suppliers in the Value Field and put the Value as Count.</SPAN></DIV><DIV> </DIV><DIV><P>Regards,<BR />Harsh Nathani</P><P><FONT face="arial black,avant garde" color="#0000FF"><STRONG>Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)</STRONG></FONT></P></DIV>Thu, 11 Jun 2020 03:35:51 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Custom-Bin-Size-to-Sum-Supplier-Amount/m-p/1151895#M17544harshnathani2020-06-11T03:35:51Z