cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Satisfy requirement from multiple warehouse

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

1 ACCEPTED SOLUTION
Super User

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 😉

7 REPLIES 7
Super User

Hello,

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

Frequent Visitor

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

Super User

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 😉

Frequent Visitor

Thanks so much! It works perfectly!

Super User

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

Frequent Visitor

Yes i did it, thanks!

Super User

Good 😉

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors