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.
I'm trying to make a report for whether you can make a master part number or not, and then which sub-assemblies can be used. I'm connecting to a Business Central DB but that part doesn't really matter when you get to PBI.
Here's a sample of what the data looks like:
Part 1 can't be made because it has shortage in inventory
Item 2 can be made but has a bottle neck on Category 4.
The biggest part is - What is available to be made or not made.
Can this be done with a measure/filter? I'm not good with filters yet.
Solved! Go to Solution.
Hello,
Create these calculated columns:
Inventory Per Category Per Item =
CALCULATE (
SUM ( 'Table'[Inventory] ),
ALLEXCEPT ( 'Table', 'Table'[Common Item Number], 'Table'[Item_Category_Code] )
)
Min Inventory in All Categories Per Item =
CALCULATE (
MIN ( 'Table'[Inventory Per Category] ),
ALLEXCEPT ( 'Table', 'Table'[Common Item Number] )
)
BOM =
//returns true if [Inventory Per Category] = [Min Inventory in All Categories Per Item]
'Table'[Inventory Per Category] = 'Table'[Min Inventory in All Categories Per Item]
Proud to be a Super User!
Hi @pcowman1
Assuming that whether an item can be done is based on sum of shortage per unique Common Item Numer and Part Number, create a calculated column similar to below:
Item Part Can Be Done =
IF (
CALCULATE (
SUM ( Table[Shortage] ),
ALLEXCEPT ( Table, 'Table'[Part Number], 'Table'[Common Item Number] )
) > 0,
"No",
"Yes"
)
Or if it is based solely on Common Item Number:
Item Can Be Done =
IF (
CALCULATE (
SUM ( Table[Shortage] ),
ALLEXCEPT ( Table, 'Table'[Common Item Number] )
) > 0,
"No",
"Yes"
)
Proud to be a Super User!
I'm trying for the second - I got No on both
Can you please post a sample data (not an image) and then your exact conditions/criteria (what can be made and based on what)? Excel formula coud also be helpful.
Proud to be a Super User!
Say I'm making these two parts - Item 1 has a bottle neck at Category 1 because there are only 2.
Item 2 has a bottle neck at Category 2 because there are only 20 and the rest of the categories are larger.
Common Item Number | Item_Category_Code | Part Number | Inventory |
Item 1 | Category 1 | Part 1 | 2 |
Item 1 | Category 2 | Part 2 | 5 |
Item 1 | Category 3 | Part 3 | 15 |
Item 1 | Category 3 | Part 4 | 31 |
Item 1 | Category 3 | Part 5 | 59 |
Item 1 | Category 5 | Part 6 | 63 |
Item 1 | Category 6 | Part 7 | 168 |
Item 1 | Category 4 | Part 8 | 254 |
Item 1 | Category 3 | Part 9 | 398 |
Item 1 | Category 3 | Part 10 | 647 |
Item 1 | Category 5 | Part 11 | 725 |
Item 1 | Category 3 | Part 12 | 2000 |
Item 2 | Category 1 | Part 13 | 50 |
Item 2 | Category 2 | Part 14 | 20 |
Item 2 | Category 4 | Part 15 | 8 |
Item 2 | Category 3 | Part 16 | 15 |
Item 2 | Category 4 | Part 17 | 25 |
Item 2 | Category 3 | Part 18 | 31 |
Item 2 | Category 5 | Part 19 | 63 |
Item 2 | Category 4 | Part 20 | 127 |
Item 2 | Category 6 | Part 21 | 168 |
Item 2 | Category 3 | Part 22 | 398 |
Item 2 | Category 3 | Part 23 | 647 |
Item 2 | Category 5 | Part 24 | 725 |
Item 2 | Category 3 | Part 25 | 2000 |
So the end result would look like this:
Common Item Number | Item_Category_Code | Inventory |
Item 1 | Category 1 | 2 |
Item 2 | Category 2 | 20 |
I'm going to add to this (and I really think this should be simple and don't know why I'm having a hard time):
if (sum(Inventory in Category) = min(sum(inventory in Category of all category sums))
Is that clearer? Am i making it worse?
Hello,
Create these calculated columns:
Inventory Per Category Per Item =
CALCULATE (
SUM ( 'Table'[Inventory] ),
ALLEXCEPT ( 'Table', 'Table'[Common Item Number], 'Table'[Item_Category_Code] )
)
Min Inventory in All Categories Per Item =
CALCULATE (
MIN ( 'Table'[Inventory Per Category] ),
ALLEXCEPT ( 'Table', 'Table'[Common Item Number] )
)
BOM =
//returns true if [Inventory Per Category] = [Min Inventory in All Categories Per Item]
'Table'[Inventory Per Category] = 'Table'[Min Inventory in All Categories Per Item]
Proud to be a Super User!
Thank you! It's late here and I've been working since early but it looks perfect! Many thanks. If I ever see you, I owe you a drink.
I'm seriously considering moving the whole thing to excel because I need to pivot a table and join it to another table. Might easier in excel where I can do vlookup on the columns but I'm really not that great with vlookup in excel either. I need to up my game in this area.
I fixed the first one by doing a second table that uses the common number and bom number and then does a calculated column filtering.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |