Routing of materials

[longhorn09]

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

[mahoneypat · Accepted 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

[mahoneypat]

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

[longhorn09]

@mahoneypat Thank you!

If I need to add a third warehouse does it look like this:

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

IF (

SELECTEDVALUE ( QOH[whse] ) = "Houston",

IF ( needed > houstonamt, houstonamt, needed ),

IF ( needed > houstonamt, needed - houstonamt,

IF ( needed > houstonamt, needed - houstonamt - knoxvilleamt,0)

))

[mahoneypat · Accepted 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

[longhorn09]

@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. 

[mahoneypat · Accepted solution]

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

[longhorn09]

@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!

[parry2k]

@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.

[longhorn09]

@parry2k  Updated it with the structure of my data and the routing the process would go through. I appreciate the help!