Forum Discussion
dw700d
Post Patron
Post PatronCalculate Sum FILTER
I am trying to create a measure that gives me the total sum when the Tract column is blank, the Loc Cd begins with Z and the AMT column is greater than 0. In the sample below, the total sum shou...
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
VahidDM
Super User
Super User
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/
dw700dPost Patron
Post PatronVahidDM 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 |