Formula help

[Snowy34]

Good evening all,

I was hoping one of you nice people could look at this issue I have with an IF statement SUMX.

What I have below is column W/Processing Days, all it should be doing is looking at all the columns starting from W/Processing Days 400 down to W/Processing Days 602 and it should return whatever the value is in one of the fields. 

IN the example below it should return 86 six times but what it has done is returned 172 4 times and 86 two times...

 

Here is my formula

W/Processing Days = IF(ISBLANK(CALCULATE(SUMx('DOP-055',[W/Processing Days 222]+'DOP-055'[W/Processing Days 249]+'DOP-055'[W/Processing Days 300]+'DOP-055'[W/Processing Days 333]+'DOP-055'[W/Processing Days 350]+'DOP-055'[W/Processing Days 400]+'DOP-055'[W/Processing Days 500]+'DOP-055'[W/Processing Days 521]+'DOP-055'[W/Processing Days 550]+'DOP-055'[W/Processing Days 601]+'DOP-055'[W/Processing Days 602]),'DOP-055'[Delivery No])),0,(CALCULATE(SUMx('DOP-055',[W/Processing Days 222]+'DOP-055'[W/Processing Days 249]+'DOP-055'[W/Processing Days 300]+'DOP-055'[W/Processing Days 333]+'DOP-055'[W/Processing Days 350]+'DOP-055'[W/Processing Days 400]+'DOP-055'[W/Processing Days 500]+'DOP-055'[W/Processing Days 521]+'DOP-055'[W/Processing Days 550]+'DOP-055'[W/Processing Days 601]+'DOP-055'[W/Processing Days 602]),'DOP-055'[Delivery No])))

 

If anyone could have a look at this and let me know what my issue is that would be great.

 

PS: Each column for W/Processing Days 500, for example, is below 

 

W/Processing Days 500 = IF('DOP-055'[Warehouse]="500",CALCULATE(SUM('Date Table formula'[500]),FILTER('Date Table formula',AND('Date Table formula'[DayMonthYear]>='DOP-055'[Item Available for Dispatch ].[Date],'Date Table formula'[DayMonthYear]<='DOP-055'[Depature Date]-1))))

 

Thank you

 

Regards 

[v-xicai · Accepted solution]

Hi @Snowy34 ,

You may change the column formula like DAX below, which deleted the group effect of 'DOP-055'[Delivery No] .

W/Processing Days =
Var d=SUMX('DOP-055', [W/Processing Days 222]+'DOP-055'[W/Processing Days 249]+'DOP-055'[W/Processing Days 300]+'DOP-055'[W/Processing Days 333]+'DOP-055'[W/Processing Days 350]+'DOP-055'[W/Processing Days 400]+'DOP-055'[W/Processing Days 500]+'DOP-055'[W/Processing Days 521]+'DOP-055'[W/Processing Days 550]+'DOP-055'[W/Processing Days 601]+'DOP-055'[W/Processing Days 602])
Return
IF(ISBLANK(d),0,d)

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.