Forum Discussion

Imrans123's avatar
Imrans123
Icon for Advocate V rankAdvocate V
4 years ago
Solved

Conditional SUM based on related table

Hi Everyone, 

 

Was hoping someone could help me out here. I built a measure that sums based on a condition from another table. The structure is as follows 

 

Transaction Table 

Columns: Transaction ID, Total Amount, Code 

 

Codes Table 

Columns: 

Code, Classification

 

The Code Table lists down all the Code and it's respective clasification which could be either "Valid", "Pending", "Ignore" or "Void". I setup the relationship between the tables and now I want to SUM all amounts which does not include "Ignore" and "Void". This would also include Codes that have not been classified (i.e. Code does not exists on the Code table)

 

Outstanding Amount = CALCULATE(SUM('Transaction'[Total Amount]), FILTER(Codes, Codes[Classification] <> "Ignore"), FILTER(Codes, Codes[Classification] <> "Void"))
 
This adds Valid and Pending, but does not include those codes that are left unclassified. Any help would be appreciated.

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Imrans123 ,

     

    Try this code.

    Outstanding Amount = 
CALCULATE (
    SUM ( 'Transaction'[Total Amount] ),
    FILTER ( ALL ( Codes ), NOT Codes[Classification] IN { "Ignore", "Void" } )
)

    Here I build a sample to have a test.

    Transaction Table:

    Codes Table:

    Based on your logic result should be A+B+E = 200+100+300+50 = 650. Result is as below. 

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Imrans123 

     

    Can you try the following measure?  Hopefully this helps:

     

    Measure =

    VAR _1 = FILTER ( ALL ( 'Codes'[Classification] <> "Ignore" && 'Codes'[Classification] <> "Void" ) )

    RETURN

    CALCULATE ( SUM ( 'Transaction'[Total Amount] ) , _1 )

    All the best

    Theo 

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

    Hi,

    Could you illustrate with an example.  Which are the ones being left out?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Imrans123 ,

     

    Try this code.

    Outstanding Amount = 
CALCULATE (
    SUM ( 'Transaction'[Total Amount] ),
    FILTER ( ALL ( Codes ), NOT Codes[Classification] IN { "Ignore", "Void" } )
)

    Here I build a sample to have a test.

    Transaction Table:

    Codes Table:

    Based on your logic result should be A+B+E = 200+100+300+50 = 650. Result is as below. 

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.