Forum Discussion

Post Patron

4 years ago

Solved

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

Need Help

Anonymous

4 years ago

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

dw700d

Post Patron

4 years ago

Ashish_Mathur I think I realize my issue, I am looking to identify when amt is greater than 0 at the Loc Code level.

So what I am really trying to do is create a measure that gives me the total sum when the Tract column is blank, the Loc Cd begins with Z and the loc Cd is greater than 0 based on the AMT column. Thanks for your help