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.
Hi All,
I would like to get some sort of sankey diagram for a routing project I am working on. To manufacture a product there is a matl required field and in another table I have the quantity of that item on hand in each of the warehouses.
Is there a way I can say if Houston has enough qty on hand to cover the matl required then send it there and if not send it to Knoxville? I have attached an email below but the example would be:
Since item 315HSS requires 8units, first check Houston (which has 0 on hand) and since it doesnt fulfill the order and Knoxville can, then send it there?
Table 1
Item | Subcomponent | Matl Qty |
A | 315HSS | 20 |
A | 320PSSGS | 15 |
A | 320PSSGS316 | 30 |
A | 325DCSSB | 8 |
A | 325GSKBUMS | 50 |
Table 2
Subcomponent | Warehouse | Qty On Hand |
315HSS | Knoxville | 50 |
320PSSGS | Houston | 3 |
320PSSGS | Knoxville | 50 |
320PSSGS316 | Houston | 60 |
320PSSGS317 | Knoxville | 90 |
325DCSSB | Houston | 10 |
325DCSSB | Knoxville | 20 |
325GSKBUMS | Houston | 30 |
325GSKBUMS | Knoxville | 60 |
So the answers would be
315HSS ---> 0 Houston ----> 20 Knoxville
210PSSGS ---> 3 Houston ---> 12 Knoxville
320PSSHS316 ---> 30 Houston
325DCSSB ---> 8 Houston
325GSKBUMS ---> 30 Houston ---> 20 Knoxville
Solved! Go to Solution.
There is probably a more elegant approach in which you add a table with warehouse and the order in which they should be considered. This is a hard-coded approach. With the two warehouses, the IF worked because if it wasn't the first warehouse, it was the other. To add more (hard coded), you should use a SWITCH function, with the appropriate IF for each
Return
SWITCH(SELECTEDVALUE(QOH[whse]),
"Houston", IF(...
"Knoxville", IF(...
"Cleveland", IF(...
For Cleveland, your IF would compare "needed" to the sum of Houston and Knoxville.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
A couple changes. Cleveland had a typo and updated the formula (Knoxville IF missing 3rd term).
Warehouse Amt =
VAR needed =
SUM ( 'Inv Bagging'[matl required] )
VAR houstonamt =
CALCULATE ( SUM ( QOH[qty on hand] ), QOH[whse] = "Houston" )
VAR knoxvilleamt =
CALCULATE ( SUM ( QOH[qty on hand] ), QOH[whse] = "Knoxville" )
VAR clevelandamt =
CALCULATE ( SUM ( QOH[qty on hand] ), QOH[whse] = "Cleveland" )
RETURN
SWITCH(SELECTEDVALUE(QOH[whse]),
"Houston", IF(needed > houstonamt, houstonamt, needed ),
"Knoxville", IF( needed - houstonamt > knoxvilleamt, knoxvilleamt, needed - houstonamt),
"Cleveland", IF((needed - houstonamt - knoxvilleamt) > clevelandamt, clevelandamt, needed - houstonamt - knoxvilleamt)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Assuming you have a relationship on Subcomponent between the tables, you can make a matrix visual with Table1[Subcomponent] on rows, Table2[Warehouse] on columns and this measure.
Warehouse Amt =
VAR needed =
SUM ( Table1[Matl Qty] )
VAR houstonamt =
CALCULATE ( SUM ( Table2[Qty On Hand] ), Table2[Warehouse] = "Houston" )
VAR knoxvilleamt =
CALCULATE ( SUM ( Table2[Qty On Hand] ), Table2[Warehouse] = "Knoxville" )
RETURN
IF (
SELECTEDVALUE ( Table2[Warehouse] ) = "Houston",
IF ( needed > houstonamt, houstonamt, needed ),
IF ( needed > houstonamt, needed - houstonamt, 0 )
)
To get this result (I showed it in a matrix, but you could use same in a Sankey)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat Thank you!
If I need to add a third warehouse does it look like this:
There is probably a more elegant approach in which you add a table with warehouse and the order in which they should be considered. This is a hard-coded approach. With the two warehouses, the IF worked because if it wasn't the first warehouse, it was the other. To add more (hard coded), you should use a SWITCH function, with the appropriate IF for each
Return
SWITCH(SELECTEDVALUE(QOH[whse]),
"Houston", IF(...
"Knoxville", IF(...
"Cleveland", IF(...
For Cleveland, your IF would compare "needed" to the sum of Houston and Knoxville.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat I tried building it this way and I am not getting the right numbers. Any last minute advise on where I am going wrong? I appears to be skipping Knoxville, but in theory I would like it to check Houston, then knoxville and then cleveland with whatever is remaining.
Warehouse Amt =
VAR needed =
SUM ( 'Inv Bagging'[matl required] )
VAR houstonamt =
CALCULATE ( SUM ( QOH[qty on hand] ), QOH[whse] = "Houston" )
VAR knoxvilleamt =
CALCULATE ( SUM ( QOH[qty on hand] ), QOH[whse] = "Knoxville" )
VAR clevelandamt =
CALCULATE ( SUM ( QOH[qty on hand] ), QOH[whse] = "Cleveland" )
RETURN
SWITCH(SELECTEDVALUE(QOH[whse]),
"Houston", IF(needed > houstonamt, houstonamt, needed ),
"Knoxville", IF( needed - houstonamt > knoxvilleamt, needed - houstonamt - knoxvilleamt),
"Cleaveland", IF((needed - houstonamt - knoxvilleamt) > (houstonamt+knoxvilleamt), needed - houstonamt - knoxvilleamt - clevelandamt,0)
)
The 3 warehouse values are below
Table 1
subcomponent | Material Req |
283139 | 11551.53 |
283187 | 929.8408 |
283210 | 5208.618 |
283250 | 4426.374 |
283275 | 144989.9 |
315GSKBUMS | 3226 |
H70110 | 245069 |
subcomponent | whse | qty on hand |
283139 | Cleveland | 28 |
283139 | Houston | 16665.65 |
283187 | Cleveland | 10 |
283187 | Houston | 2441.944 |
283210 | Cleveland | 10 |
283210 | Houston | 8631.955 |
283250 | Cleveland | 10 |
283250 | Houston | 4797.266 |
283275 | Cleveland | 10 |
283275 | Houston | 13938.26 |
315GSKBUMS | Cleveland | 0 |
315GSKBUMS | Houston | 1220 |
315GSKBUMS | Knoxville | 12589 |
H70110 | Cleveland | 108999 |
H70110 | Houston | 235646 |
H70110 | Knoxville | 0 |
Final output:
0283139 -- > 11551.53 Houston
0283187 --> 929.84 Houston
0283210 --> 5208.62 Houston
0283250 --> 4426.37 Houston
0283275 --> 12938.26 Houston --> 0 Knoxville --> 10 Cleveland --> 131041.67 Leftover
315GSKBUM --> 1220 Houston -->2006 Knoxville
H70110 --> 235646 Houston --> 0 Knoxville -->9423 Cleveland
That data should hand all the scenarios that could possibly come from their routing.
A couple changes. Cleveland had a typo and updated the formula (Knoxville IF missing 3rd term).
Warehouse Amt =
VAR needed =
SUM ( 'Inv Bagging'[matl required] )
VAR houstonamt =
CALCULATE ( SUM ( QOH[qty on hand] ), QOH[whse] = "Houston" )
VAR knoxvilleamt =
CALCULATE ( SUM ( QOH[qty on hand] ), QOH[whse] = "Knoxville" )
VAR clevelandamt =
CALCULATE ( SUM ( QOH[qty on hand] ), QOH[whse] = "Cleveland" )
RETURN
SWITCH(SELECTEDVALUE(QOH[whse]),
"Houston", IF(needed > houstonamt, houstonamt, needed ),
"Knoxville", IF( needed - houstonamt > knoxvilleamt, knoxvilleamt, needed - houstonamt),
"Cleveland", IF((needed - houstonamt - knoxvilleamt) > clevelandamt, clevelandamt, needed - houstonamt - knoxvilleamt)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat Thank you very much. This is perfect. I threw it in another calculation to say if that measure was < 0 then make it 0 because it was providing - values for Cleveland but other than it fulfills what I am trying to!
@longhorn09 it will be easier if you share how your data table looks like, a lot will depend on that, visualization is an easy part.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k Updated it with the structure of my data and the routing the process would go through. I appreciate 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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |