cancel
Showing results for
Did you mean:
Frequent Visitor

## how to deduct value from calculate & filter

Struggling to figure out how to deduct some of the condition value from the sum

Below is the formula i'm using and error message i receive

"A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Actual putaway Qty = CALCULATE(SUM('In- MSEG'[In-LTAP.Dest act. qty]),FILTER(all('In- MSEG'),'In -LIKP'[Delivery]='In- MSEG'[Delivery])
-CALCULATE(sum('In- MSEG'[In-LTAP.Dest act. qty]),filter('In-LTAP','In-LTAP'[ Source bin Type]= 922 && 'In-LTAP'[Dest. Type] = "HR1"),
Filter('In-LTAP','In-LTAP'[ Source bin Type]= 922 && 'In-LTAP'[Dest. Type] = "SR1"),
Filter('In-LTAP','In-LTAP'[ Source bin Type]= 922 && 'In-LTAP'[Dest. Type] = "FL1")))

the data set error
1 ACCEPTED SOLUTION
Community Support

Hi @nebiew ,

I think you are missing a parenthesis in the formula before the minus sign. And you might combine the 3 filter() function in the second calculate() function.

Refer:

``````Actual putaway Qty =
CALCULATE(SUM('In- MSEG'[In-LTAP.Dest act. qty]),FILTER(all('In- MSEG'),'In -LIKP'[Delivery]='In- MSEG'[Delivery]))
-CALCULATE(sum('In- MSEG'[In-LTAP.Dest act. qty]),filter('In-LTAP','In-LTAP'[ Source bin Type] = 922 && 'In-LTAP'[Dest. Type] in {"HR1","SR1","FL1"}))``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Community Support

Hi @nebiew ,

I think you are missing a parenthesis in the formula before the minus sign. And you might combine the 3 filter() function in the second calculate() function.

Refer:

``````Actual putaway Qty =
CALCULATE(SUM('In- MSEG'[In-LTAP.Dest act. qty]),FILTER(all('In- MSEG'),'In -LIKP'[Delivery]='In- MSEG'[Delivery]))
-CALCULATE(sum('In- MSEG'[In-LTAP.Dest act. qty]),filter('In-LTAP','In-LTAP'[ Source bin Type] = 922 && 'In-LTAP'[Dest. Type] in {"HR1","SR1","FL1"}))``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks