cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Custom Bin Size to Sum Supplier Amount

Hello. I would like to create 5 bins: 0-999, 1000-4,999 , 5,000-49,999, 50,000-499,999, >500,000

 

I would like to get the grand total of each individual supplier and then distinct count each supplier within each of the bins.

 

Below is some example data: 

SupplierBusiness UnitCategoryInvoice DetailsInvoice Amount
AustinSecondsolidspacer$555,500.00
DallasFourthsolidheat pump$4,440.00
DelawareSecondsolidpump$20.00
RaleighThirdservicespacer$500.00
RaleighThirdservicespacer$4,400.00
RaleighThirdserviceheat pump$3,000.00
ChicagoSecondmetalplate$555.00
BrooklynSecondmetalspacer$450.00
Times SquareFourthmetalheat pump$4,400.00
Washington D.CFourthmetalpump$500.00
HoustonFirstmetalgasket$3,000.00
RaleighThirdmetalplate$300.00
ChicagoSecondmetalpump$222.00
BrooklynSecondmetalspacer$322.00
DallasFirstmetalgloves$450,000.00
ChicagoThirdmetalgloves$4,000.00
BrooklynSecondmetalplate$430,000.00
Times SquareFourthmetalspacer$3,400.00
Washington D.CFirstmetalheat pump$530.00
Washington D.CFourthMachinegloves$200.00
WyomingSecondMachinescrew$2,000.00
Washington D.CFourthMachinescrew$540.00
HoustonFirstfabricscrew$80,000.00
RaleighFirstfabricgasket$200.00
ChicagoSecondfabricscrew$5,600.00
AustinFivefabricgasket$332.00
DallasThirdfabricgasket$400.00
ChicagoFourthfabricheat pump$345.00
HoustonThirdfabricgasket$200.00
RaleighFivefabricgasket$30,000.00
ChicagoFourthfabricgloves$54,000.00
AustinSecondfabricplate$1,200.00
DallasThirdfabricspacer$340.00

 

I would like to get it to this: 

Row LabelsSum of Invoice AmountBins
Austin $                 557,032.00Greater than 500,000
Brooklyn $                 430,772.00Between 50000 and 500000
Chicago $                   64,722.00Between 50000 and 500000
Dallas $                 455,180.00Between 50000 and 500000
Delaware $                          20.00Less Than 1000
Houston $                   83,200.00Between 50000 and 500000
Raleigh $                   38,400.00Between 50000 and 500000
Times Square $                     7,800.00Between 5000 and 50000
Washington D.C $                     1,770.00Less than 5000
Wyoming $                     2,000.00Less Than 5000

 

  

I would greatly appreciate any help. Thank you.

5 REPLIES 5
Highlighted
Super User V
Super User V

Re: Custom Bin Size to Sum Supplier Amount

Hi @rgold25 ,

 

Create 2 measures

 

Sum of Invoices = CALCULATE(SUM('Table'[Invoice Amount]),ALLEXCEPT('Table','Table'[Supplier]))
 
Bin =
SWITCH(
TRUE(),
'Table'[Sum of Invoices] >= 500000,"Greater than 500,000",
'Table'[Sum of Invoices] > 50000 && 'Table'[Sum of Invoices] <=499999,"Between 50000 and 500000",
'Table'[Sum of Invoices] > 5000 && 'Table'[Sum of Invoices] <=49999,"Between 5000 and 50000",
'Table'[Sum of Invoices]> 1000 && 'Table'[Sum of Invoices] <=4999,"Between 1000 and 5000",
'Table'[Sum of Invoices] <= 999,"Between 0 and 1000"
)
 
 
1.jpg
 
Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Frequent Visitor

Re: Custom Bin Size to Sum Supplier Amount

Thank you for the help @harshnathani . 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. 

Highlighted
Super User V
Super User V

Re: Custom Bin Size to Sum Supplier Amount

Hi @rgold25 ,

 

Create a Calulated Column then.

 

BIN Name =

SWITCH(
TRUE(),
'Table6'[Sum of Invoices] >= 500000,"Greater than 500,000",
'Table6'[Sum of Invoices] > 50000 && 'Table6'[Sum of Invoices] <=499999,"Between 50000 and 500000",
'Table6'[Sum of Invoices] > 5000 && 'Table6'[Sum of Invoices] <=49999,"Between 5000 and 50000",
'Table6'[Sum of Invoices]> 1000 && 'Table6'[Sum of Invoices] <=4999,"Between 1000 and 5000",
'Table6'[Sum of Invoices] <= 999,"Between 0 and 1000")
 
1.jpg
 
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted
Frequent Visitor

Re: Custom Bin Size to Sum Supplier Amount

Hello @harshnathani 

 

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?

 

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?

 

Thank you again for the help.

Highlighted
Super User V
Super User V

Re: Custom Bin Size to Sum Supplier Amount

Hi @rgold25 ,

 

Not sure why you have been getting the same values in all you have been getting the same value.

 

Link to the file:

https://drive.google.com/file/d/1pkH0zXiTMMnIwqm_XqmYkki5LKR3QY9e/view?usp=sharing

 

Calculated Column

BIN Name =

 

SWITCH(
TRUE(),
'Table6'[Sum of Invoices] >= 500000,"Greater than 500,000",
'Table6'[Sum of Invoices] > 50000 && 'Table6'[Sum of Invoices] <=499999,"Between 50000 and 500000",
'Table6'[Sum of Invoices] > 5000 && 'Table6'[Sum of Invoices] <=49999,"Between 5000 and 50000",
'Table6'[Sum of Invoices]> 1000 && 'Table6'[Sum of Invoices] <=4999,"Between 1000 and 5000",
'Table6'[Sum of Invoices] <= 999,"Between 0 and 1000")
 
Incase you want the count of the Suppliers you can just pull the Suppliers in the Value Field and put the Value as Count.
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors