Forum Discussion

alexei7's avatar
alexei7
Continued Contributor
10 years ago

Help with SUMIF-like DAX

Hi,   I'm trying to create a formula which will calculate the sum of income (from the income table) where a URN in the income table matches a URN in the registrations table   In Excel-lingo this ...
Need Help
Sean's avatar
Sean
Community Champion
10 years ago

the general formula will be something like this...

 

Measure = CALCULATE(SUM(Table[Column]), Filter(s).....)

 

but how you list the filters depends on what you are trying to accomplish

 

you need to provide more details

  • alexei7's avatar
    alexei7
    Continued Contributor
    10 years ago

    Thanks Sean

     

    I have two tables:

     

    1. Registrations (columns include "URN", "Registration Date" as well as other fields relating to the registration. URN is not unique/primary key)

    2. Income (columns include "URN", "Income Amount", "Income date" as well as other field relating to the income. Again, URN is not unique/primary key).

     

    I'm trying to create a measure which will give me the income from the Income table where the URN is also present in the Registrations table.

     

     

     

    • alexei7's avatar
      alexei7
      Continued Contributor
      10 years ago

       

      I seem to have got this working 

       

      I created a lookup column ("RegisteredURN"), using LOOKUPVALUE in the Income table to lookup the URN from a unique version of the URNs from the Registrations table.

       

      Then I created a measure in the Income table:

      CALCULATE(sum(Income[Income Amount]),Income[RegisteredURN]<>"")

       

      • Sean's avatar
        Sean
        Community Champion
        10 years ago

        alexei7 Great looks like you got it. SUM [Income Amount] for all non blank [URN]