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

Good day Ashish_Mathur see below

Transaction	loc Cd	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
10	Z9999	-10	ABC
11	Z9999	30	ABC
12	A1234	20
13	A1234	30
14	Z1111	40
15	Z1111	70
16	Z3333	100	ABC
17	Z3333	20	ABC
18	V1111	-20
19	V1111	-25
20	V1111	50
21	Z8888	50	ABC
22	Z8888	-10	ABC

The end result is in the pivot table visual below. It shows Loc Cd's that begin with Z, have a blank in the tract column and the sum of all the individual transactions affilliated with that loc cd is greater than 0