Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Supplier | Business Unit | Category | Invoice Details | Invoice Amount |
Austin | Second | solid | spacer | $555,500.00 |
Dallas | Fourth | solid | heat pump | $4,440.00 |
Delaware | Second | solid | pump | $20.00 |
Raleigh | Third | service | spacer | $500.00 |
Raleigh | Third | service | spacer | $4,400.00 |
Raleigh | Third | service | heat pump | $3,000.00 |
Chicago | Second | metal | plate | $555.00 |
Brooklyn | Second | metal | spacer | $450.00 |
Times Square | Fourth | metal | heat pump | $4,400.00 |
Washington D.C | Fourth | metal | pump | $500.00 |
Houston | First | metal | gasket | $3,000.00 |
Raleigh | Third | metal | plate | $300.00 |
Chicago | Second | metal | pump | $222.00 |
Brooklyn | Second | metal | spacer | $322.00 |
Dallas | First | metal | gloves | $450,000.00 |
Chicago | Third | metal | gloves | $4,000.00 |
Brooklyn | Second | metal | plate | $430,000.00 |
Times Square | Fourth | metal | spacer | $3,400.00 |
Washington D.C | First | metal | heat pump | $530.00 |
Washington D.C | Fourth | Machine | gloves | $200.00 |
Wyoming | Second | Machine | screw | $2,000.00 |
Washington D.C | Fourth | Machine | screw | $540.00 |
Houston | First | fabric | screw | $80,000.00 |
Raleigh | First | fabric | gasket | $200.00 |
Chicago | Second | fabric | screw | $5,600.00 |
Austin | Five | fabric | gasket | $332.00 |
Dallas | Third | fabric | gasket | $400.00 |
Chicago | Fourth | fabric | heat pump | $345.00 |
Houston | Third | fabric | gasket | $200.00 |
Raleigh | Five | fabric | gasket | $30,000.00 |
Chicago | Fourth | fabric | gloves | $54,000.00 |
Austin | Second | fabric | plate | $1,200.00 |
Dallas | Third | fabric | spacer | $340.00 |
I would like to get it to this:
Row Labels | Sum of Invoice Amount | Bins |
Austin | $ 557,032.00 | Greater than 500,000 |
Brooklyn | $ 430,772.00 | Between 50000 and 500000 |
Chicago | $ 64,722.00 | Between 50000 and 500000 |
Dallas | $ 455,180.00 | Between 50000 and 500000 |
Delaware | $ 20.00 | Less Than 1000 |
Houston | $ 83,200.00 | Between 50000 and 500000 |
Raleigh | $ 38,400.00 | Between 50000 and 500000 |
Times Square | $ 7,800.00 | Between 5000 and 50000 |
Washington D.C | $ 1,770.00 | Less than 5000 |
Wyoming | $ 2,000.00 | Less Than 5000 |
|
I would greatly appreciate any help. Thank you.
Hi @Anonymous ,
Create 2 measures
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.
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
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |