Forum Discussion
Calculate Sum FILTER
Hi dw700d ,
Check the measures.
Include<0:
Measure = CALCULATE(SUM('Table'[AMT]),FILTER(ALLSELECTED('Table'),'Table'[Tract]=BLANK()&&'Table'[loc Cd]=SELECTEDVALUE('Table'[loc Cd])&&LEFT(SELECTEDVALUE('Table'[loc Cd]),1)="Z"))Aggregate value>0:
Measure 2 = var flag = CALCULATE(SUM('Table'[AMT]),FILTER(ALLSELECTED('Table'),'Table'[Tract]=BLANK()&&'Table'[loc Cd]=SELECTEDVALUE('Table'[loc Cd])&&LEFT(SELECTEDVALUE('Table'[loc Cd]),1)="Z")) return IF(flag>0,flag,BLANK())Best Regards,
Jay
VahidDMThanks for the response. I would like a measure that identifies any Loc CD with A first letter that begins with Z, where the Tract column = blank and any Loc CD where the aggreagte value is greater than 0. In the example below I have two "Loc CD's" Z1234 & Z5678 the measure would only return an amount for "Loc CD" Z5678 because the aggregate value of all its transacations is greater than 0 (-50,-10,20,80). The amount would be 40.
The measure would not return an amount for Z1234 because the aggregate value of all Z1234 transactions is negative (100,-70,-100,20)
| Index | LocCD | Amt | Tract |
| 1 | Z1234 | 100 | |
| 2 | Z1234 | -70 | |
| 3 | Z1234 | -100 | |
| 4 | Z1234 | 20 | |
| 6 | Z5678 | -50 | |
| 7 | Z5678 | -10 | |
| 8 | Z5678 | 20 | |
| 9 | Z5678 | 80 |
Does this help?
Try this measue:
Measure = VAR _A = FILTER ( SUMMARIZE ( 'Table', 'Table'[LocCD], "S", CALCULATE ( SUM ( 'Table'[Amt] ), FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[Trac] ) ) ) ) ), [S] > 0 ) RETURN SUMX ( _A, [S] )output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/VahidDM thank you for working with me. Something is a bit off, this measure is only giving me data where the "Tract" column contains information. I need the "Tract" column to be blank. How would I tweak this measure to accomplish that? In the example below "Loc Cd" Z0000 would not return a value because the "Tract" column is not blank
Index LocCD Amt Tract 1 Z0000 20 ABC 2 Z1234 100 3 Z1234 -70 4 Z1234 -100 5 Z1234 20 6 Z5678 -50 7 Z5678 -10 8 Z5678 20 9 Z5678 80
