Dax novice here...I need help creating a measure: SPLIT_DROP_DEMAND... on excel this will be done with a SUMIFS: =F2/SUMIFS(F:F,D:D,D2,C:C,C2,A:A,A2)*G2 So the drop_demand is joined on a warehouse-sku-period level, need a measure that will split it down to the shipto level, based on the demand quantity.See sample table:WAREHOUSESHIP_TOSKUPERIOD_YEARSALES_DIVISIONDEMANDDROP_DEMAND SPLIT_DROP_DEMAND123456789216655441123P12-2023LOCAL965,3761,797,480 541,192123451533686655441123P12-2023LOCAL521,6401,797,480 292,432123451233546655441123P12-2023LOCAL123,4441,797,480 69,203123458866976655441123P12-2023LOCAL154,1521,797,480 86,418123458633216655441123P12-2023LOCAL236,1241,797,480 132,372123457788566655441123P12-2023LOCAL213,8761,797,480 119,899123457896326655441123P12-2023LOCAL260,1361,797,480 145,833123451234556655441123P12-2023LOCAL401,2201,797,480 224,925123451536356655441123P12-2023LOCAL330,3721,797,480 185,207

Hi, This calculated column formula works Split drop demnd = DIVIDE(Data[DEMAND],CALCULATE(SUM(Data[DEMAND]),FILTER(Data,Data[WAREHOUSE]=EARLIER(Data[WAREHOUSE])&&Data[SKU]=EARLIER(Data[SKU])&&Data[PERIOD_YEAR]=EARLIER(Data[PERIOD_YEAR]))))*Data[DROP_DEMAND] Hope this helps.

Live_Mace

Frequent Visitor

2 years ago

Solved

SUMIFS Calculation with Dax

Dax novice here...

I need help creating a measure: SPLIT_DROP_DEMAND... on excel this will be done with a SUMIFS: =F2/SUMIFS(F:F,D:D,D2,C:C,C2,A:A,A2)*G2

So the drop_demand is joined on a warehouse-sku-period level, need a measure that will split it down to the shipto level, based on the demand quantity.

See sample table:

WAREHOUSE	SHIP_TO	SKU	PERIOD_YEAR	SALES_DIVISION	DEMAND	DROP_DEMAND	SPLIT_DROP_DEMAND
12345	678921	6655441123	P12-2023	LOCAL	965,376	1,797,480	541,192
12345	153368	6655441123	P12-2023	LOCAL	521,640	1,797,480	292,432
12345	123354	6655441123	P12-2023	LOCAL	123,444	1,797,480	69,203
12345	886697	6655441123	P12-2023	LOCAL	154,152	1,797,480	86,418
12345	863321	6655441123	P12-2023	LOCAL	236,124	1,797,480	132,372
12345	778856	6655441123	P12-2023	LOCAL	213,876	1,797,480	119,899
12345	789632	6655441123	P12-2023	LOCAL	260,136	1,797,480	145,833
12345	123455	6655441123	P12-2023	LOCAL	401,220	1,797,480	224,925
12345	153635	6655441123	P12-2023	LOCAL	330,372	1,797,480	185,207

Hi,

This calculated column formula works

Split drop demnd = DIVIDE(Data[DEMAND],CALCULATE(SUM(Data[DEMAND]),FILTER(Data,Data[WAREHOUSE]=EARLIER(Data[WAREHOUSE])&&Data[SKU]=EARLIER(Data[SKU])&&Data[PERIOD_YEAR]=EARLIER(Data[PERIOD_YEAR]))))*Data[DROP_DEMAND]

Hope this helps.

Forum Discussion

SUMIFS Calculation with Dax

3 Replies

Related content

SumIf

sumif not working

SUMIFS

SUMIFS equivalent for calculated column

SUMIF Function