Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
@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.
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?
@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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |