Dax distribute positive number

[Anonymous]

hello,

Im trying to create a dax measure to check if a product  that has excess inventory in a certain warehouse location , have that amount be distributed to the  rest of the ware house locations dependending on there demand. Basically if im overstocked send that stock to areas that need it, if the need it

heres an example table

Geo	SKU	Inv OH	Backlog	Open POs	Forecast	available	required	overstock/understock
USA	G2359	6662	51	0	12000	6662	12051	-5389
JAPAN	G2359	59115	29	80855	6000	139970	6029	133941
France	G2359	0	113	1810	13100	1810	13213	-11403

in this table you can see the a  certain sku is avalible in 3 warehoused . I added 2 columms , avalible(inv oh + open po) and required( backlog + forecast). The i added overstock understock  column(avalible - required) to see if there are areas with excess inventory. This is where im stuck , my idea is somethin like this:

Geo	SKU	Inv OH	Backlog	Open POs	Forecast	available	required	overstock/understock	new sugested distribution column	total
USA	G2359	6662	51	0	12000	6662	12051	-5389	5389	0
JAPAN	G2359	59115	29	80855	6000	139970	6029	133941	0	117149
France	G2359	0	113	1810	13100	1810	13213	-11403	11403	0

basically i add two new columms , the first one which analysis if there is overstock(meaning positive number) and sends that to the understocked areas( negative numbers), in this example we see on region with 133941 units of excess but to others have 5389 and  11403 respectivly of under stock. So we take 16792 from  the japan area, and send 5389 to USA aand 11403 to France. To total would add up the the remains, in this case we would still have on region overstocked but the rest would have met there demands , meaning theres a zero instead of a negative or positive

My idea is to use a dax measure using calculate function combined with a group by since i belive we need a way to filte sku by region since one sku can be in up to 3 different regions but my knowlegde of the calculate funtion is rather elementary at this point,

Please let me know if you have any queestions an thanks in advance 😃

[v-shex-msft]

HI @Anonymous ,

I haven't found any category or index fields can be used as group for summary and distribute to negative value, can you please share more detail about these?

Regards,

Xiaoxin Sheng

[Anonymous]

the key should be the combination of the SKU an the Region i belive

[Anonymous]

i can also add a table with jsut the SKU list which would be unique, and create that realtion ship in the model

[Anonymous]

i added a key column , a contatination of sku and region

key	Geo	SKU	Inv OH	Backlog	Open POs	Forecast	available	required	overstock/understock	new sugested distribution column	total
G2359USA	USA	G2359	6662	51	0	12000	6662	12051	-5389	5389	0
G2359JAPAN	JAPAN	G2359	59115	29	80855	6000	139970	6029	133941	0	117149
G2359France	France	G2359	0	113	1810	13100	1810	13213	-11403	11403	0

[v-shex-msft]

Hi @Anonymous ,

You can try to use following measure formula, it will summarize records based on current geo and sku group:

Measure =
CALCULATE (
    SUM ( Table[Forcast] ) - SUM ( Table[Avallable] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Gep] ),
    VALUES ( Table[SKU] )
)

Regards,

Xiaoxin Sheng