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

[Anonymous]

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!

[v-yuta-msft]

@Anonymous,

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.

[v-yuta-msft]

@Anonymous ,

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.

[Anonymous]

@v-yuta-msft ,

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"

)

)

[Anonymous]

@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"

)

)