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)
Highlighted
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. 

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
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors