Satisfy requirement from multiple warehouse

[Saaam]

Hi,

how can i achieve something like this?

These are my requirement and if i can satisfy them picking qty from different warehouse i should suggest that option!

Thanks

[Gabry]

Hello,

sure you can achive that. What's your issue on this? Are you looking for dax formula or something else?

Do you have a model on Powerbi? Please provide more information.

[Saaam]

Hi, i'm looking for a dax measure!

The warehouse have priority order WH1 -> WH2 -> WH3 -> WH4

I'm tryng to do some cumulative sum but i don't know how to deal with the remainder stock 

The model is just like the image! Item (with rank for requirements priority) and each warehouse stock

Thanks

[Gabry · Accepted solution]

Ok so I think the measure should look like this:

Result =

VAR RequiredQty = 'Foglio1'[REQ QTY]

VAR WH1_Stock = 'Foglio1'[WH1 stock]

VAR WH2_Stock = 'Foglio1'[WH2 stock]

VAR WH3_Stock = 'Foglio1'[WH3 stock]

VAR WH4_Stock = 'Foglio1'[WH4 stock]

VAR TakeFromWH1 = IF(WH1_Stock >= RequiredQty, RequiredQty, WH1_Stock)

VAR RemainingAfterWH1 = RequiredQty - TakeFromWH1

VAR TakeFromWH2 = IF(WH2_Stock >= RemainingAfterWH1, RemainingAfterWH1, WH2_Stock)

VAR RemainingAfterWH2 = RemainingAfterWH1 - TakeFromWH2

VAR TakeFromWH3 = IF(WH3_Stock >= RemainingAfterWH2, RemainingAfterWH2, WH3_Stock)

VAR RemainingAfterWH3 = RemainingAfterWH2 - TakeFromWH3

VAR TakeFromWH4 = IF(WH4_Stock >= RemainingAfterWH3, RemainingAfterWH3, WH4_Stock)

VAR RemainingAfterWH4 = RemainingAfterWH3 - TakeFromWH4

RETURN

IF(TakeFromWH1>0, "Take " & TakeFromWH1 & " from WH1", IF(TakeFromWH1+TakeFromWH2+TakeFromWH3+TakeFromWH4>0,"Take ", " ") &

IF(TakeFromWH2 > 0,   TakeFromWH2 & " from WH2", "") &

IF(TakeFromWH3 > 0,   TakeFromWH3 & " from WH3", "") &

IF(TakeFromWH4 > 0, TakeFromWH4 & " from WH4", "") &

IF(RemainingAfterWH4 > 0, " order " & RemainingAfterWH4, "")

)

Don't forget to kudos and accept the solution 😉

[Saaam]

Thanks so much! It works perfectly!

[Gabry]

Forgot to mention that it is a calculated column if you need a measure it needs some changes

[Saaam]

Yes i did it, thanks!

[Gabry]

Good 😉