Forum Discussion

dw700d's avatar
dw700d
Icon for Post Patron rankPost 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's avatar
    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

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

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's avatar
dw700d
Icon for Post Patron rankPost Patron
4 years ago

Good day Ashish_Mathur see below

 

Transactionloc CdAMT       Tract              
1Z000020       ABC       
2Z1234100 
3Z1234-70 
4Z1234-100 
5Z123420 
6Z5678-50 
7Z5678-10 
8Z567820 
9Z567880 
10Z9999-10ABC
11Z999930ABC
12A123420 
13A123430 
14Z111140 
15Z111170 
16Z3333100ABC
17Z333320ABC
18V1111-20 
19V1111-25 
20V111150 
21Z888850ABC
22Z8888-10ABC

 

 

 

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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Here's a simple solution