Forum Discussion
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 should be 120 based on my criteria
| Index | Loc CD | Amt | Tract |
| 1 | Z1234 | 100 | |
| 2 | Z1234 | -70 | |
| 3 | Z1234 | -100 | |
| 4 | Z1234 | 20 | |
| 5 | Z1234 | 10 | 123 |
| 6 | Q3344 | 20 |
I tried using the formula below but something isn’t working correctly. Any suggestions would be greatly appreciated
Wireless Outside QOZ = CALCULATE(SUM('BIP+ In service'[Amt]),
FILTER(' BIP+ In service',' BIP+ In service'[Tract] = BLANK()
),
FILTER(' BIP+ In service',LEFT(' BIP+ In service'[Loc Cd],1)="Z"),
FILTER(' BIP+ In service',' BIP+ In service'[Amt]>0))
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
10 Replies
Hi,
Try these measures:
Amount = SUM(Data[Amt])Measure1 = CALCULATE([Amount],FILTER(Data,Data[Amount]>0&&Data[Tract]=BLANK()&&LEFT(Data[Loc CD],1)="Z"))Drag Measure1 to your visual.
Hope this helps.
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
What does this mean: loc Cdis greater than 0
can you can add a expected output as well?
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
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
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
