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

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

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