cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Striggs Frequent Visitor

## How to Calculate Sumx with a Filter and Subtract from another Column w/ Multiple Filters

Hello,

I am having trouble to understand what should be done when needing to sum Column A and then subtract this amount by a Column B which might have filters as well. Also I noted in cases when subtracting Column A from Column B, if a category doesn’t exist in Column A but is present in Column B, it will not show that value thats listed in Column B(which would be - XXXX) .

Below I have an example where I need to Sum labels that are “blank” in this column and subtract it by all other labels in this same column EXCEPT A (Reason for this is that this “A” label will be used in a different measure and since "blanks" is part of the equation it should be excluded from the subtraction as well. ) The formula I have thus far is :

AR = CALCULATE(

SUMX(FILTER('FBL5N', 'FBL5N'[Special G/L ind.] = ""), 'FBL5N'[fbl5n Amount in doc. curr.] ) ) -

CALCULATE(SUMX(FBL5N),

ALLEXCEPT(???)

I'm able to get the sum of the blanks (AR column) using the first "Calculate", but is the correct format when wanting the subtract 2 sets of filtered results? Also most importantly how would it be best for the 2nd half to be written when I need to exclude the blank labels and "A" labels? All other labels would be considered ( E , Z, etc...) for summation. Thank you in advance for the support!

4 REPLIES 4 Community Support Team

## Re: How to Calculate Sumx with a Filter and Subtract from another Column w/ Multiple Filters

To be general, maybe you could write the second part measure like below:

```AR =
CALCULATE (
SUMX (
FILTER ( 'FBL5N', 'FBL5N'[Special G/L ind.] = "" ),
'FBL5N'[fbl5n Amount in doc. curr.]
)
)
- CALCULATE (
SUM ( FBL5N[Special G/L ind.] ),
FILTER (
ALLEXCEPT ( FBL5N, FBL5N[Label] ),
FBL5N[Label] <> BLANK ()
&& FBL5N[Label] <> "A"
)
)

```

Community Support Team _ Jimmy Tao

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

Striggs Frequent Visitor

## Re: How to Calculate Sumx with a Filter and Subtract from another Column w/ Multiple Filters

@v-yuta-msft, thank you for the response. Actually I adjusted the 2nd half of the measure to how I think it should be, but I am unable to use it. You can see below that the all except should come from the same column "special G/L ind" and the value to be summed is from the "fbl5n Amount in doc. curr." Sorry if I caused any confusion using the word "label" in reality I was referring to the various letters that are given in the Special G/L ind column. Do you know what would be the reason for this error that I have in the picture regarding the Special G/L ind column? It seems it is due to how the ALLEXCEPT was written?

AR = CALCULATE(
SUMX(
FILTER('FBL5N', 'FBL5N'[Special G/L ind.] = "" ),
'FBL5N'[fbl5n Amount in doc. curr.]
)
)
- CALCULATE(
SUM( FBL5N[fbl5n Amount in doc. curr.] ),
FILTER(
ALLEXCEPT( FBL5N, FBL5N[Special G/L ind.] ),
FBL5N[Special G/L ind.] <> BLANK()
&& FBL5N[Special G/L ind.] <> "A"
)
)  Community Support Team

## Re: How to Calculate Sumx with a Filter and Subtract from another Column w/ Multiple Filters

This may caused by the second filter affect the first filter, add an ALL() function to the first and try again.

```AR =
CALCULATE (
SUMX (
FILTER ( ALL('FBL5N'), 'FBL5N'[Special G/L ind.] = "" ),
'FBL5N'[fbl5n Amount in doc. curr.]
)
)
- CALCULATE (
SUM ( FBL5N[Special G/L ind.] ),
FILTER (
ALLEXCEPT ( FBL5N, FBL5N[Label] ),
FBL5N[Label] <> BLANK ()
&& FBL5N[Label] <> "A"
)
)
```

Community Support Team _ Jimmy Tao

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

Striggs Frequent Visitor

## Re: How to Calculate Sumx with a Filter and Subtract from another Column w/ Multiple Filters

I included the ALL function to the first filter and I'm still receiving the alert: "A single value for column 'Special G/L ind.' in table 'FBL5N' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Should SUMX be used rather than the SUM function in the 2nd filter?

AR = CALCULATE(
SUMX(
FILTER( All('FBL5N'), 'FBL5N'[Special G/L ind.] = "" ),
'FBL5N'[fbl5n Amount in doc. curr.]
)
)
- CALCULATE(
SUM( FBL5N[fbl5n Amount in doc. curr.] ),
FILTER(
ALLEXCEPT( FBL5N, FBL5N[Special G/L ind.] ),
FBL5N[Special G/L ind.] <> BLANK()
&& FBL5N[Special G/L ind.] <> "A"
)
) ## Helpful resources

Announcements
Top Ideas Top Kudoed Authors
Users Online
Currently online: 31 members 860 guests
Recent signins:
• Sergiy • EV • • 365Sdx • • pradeepall4u • parvindar Please welcome our newest community members:
• bharat1236 • MCKNEEZY • kcrossley • mfquinn66 • juaneduardo • demircimerve • hruiz0422 