Forum Discussion

alexei7's avatar
alexei7
Icon for Continued Contributor rankContinued 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 would be something like:

 

"=sumif(registrationsURN, incomeURN, incomeAmount)"

 

Can anyone help?

Thanks

 

Need Help

5 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity 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
      Icon for Continued Contributor rankContinued 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
        Icon for Continued Contributor rankContinued 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]<>"")

         

  • wonga's avatar
    wonga
    Icon for Continued Contributor rankContinued Contributor
    10 years ago

    Establishing a relationship between the two tables should do the trick in terms of getting the "If table1[urn] = table2[urn] then sum(income)"