Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

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)
Anonymous
Not applicable

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. 

Hi @Anonymous ,

 

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)

Hi @Anonymous ,

 

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)

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors