Calculate Sum FILTER

[dw700d]

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))

[Anonymous · Accepted solution]

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]

@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?

[VahidDM]

@dw700d 

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/

[dw700d]

@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

[Ashish_Mathur]

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.

[dw700d]

@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

[Ashish_Mathur]

Hi,

Please take a comprehensive example to pressent your case.  Take multiple LocCD's and for those show entries in the Tract numbers as well.  On that comprehensive example, show the expected result.

[dw700d]

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

[Ashish_Mathur]

Hi,

Here's a simple solution

[VahidDM]

@dw700d 

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/