SUMIFS Calculation with Dax

[Live_Mace]

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

[Ashish_Mathur · Accepted solution]

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]

Thanks @Ashish_Mathur this works!

[Ashish_Mathur]

You are welcome.