cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Striggs Frequent Visitor
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. )

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
Community Support Team
Community Support Team

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

@Striggs,

 

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

 

 

 

Community Support Team
Community Support Team

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

@Striggs ,

 

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
Frequent Visitor

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

@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

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 31 members 860 guests
Please welcome our newest community members: