Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. )

image.png

 

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.

 

image.png

 

  

Thank you in advance for the support!

 

 

4 REPLIES 4
v-yuta-msft
Community Support
Community Support

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

 

 

 

@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
Not applicable

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

 

 

image.png

 

Anonymous
Not applicable

@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"
)
)
 image.png

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.