Forum Discussion

ntravis's avatar
ntravis
Frequent Visitor
10 years ago
Solved

Excel user sumif problem in DAX

I am having trouble with the CALCULATE function currently.  I am a heavy Excel user that is trying to transition into Power BI and DAX to further my analytical capabilities.   Given the two tables below, I am trying to calculate the total expected revenue for a job.

 

my current formula would be as follows:

 

Revenue = Calculate(

                             sum(Estimate[Amount])  +  sum(Adjustments[Amount]),

                                             Estimates[Type]="R",

                                             Adjustments[Type]="R")

 

This formulae works for the examples of job 1000 and 1002.  But, where job 1001 has no Adjustments, the formulae is just resulting in nothing.

 

I know how to tackle this in excel, but I am sure DAX can make this easier for me.   Please let me know your thoughts on how to correct this.

 

Thanks in Advance

 

Estimate
JobTypeAmount
1000E   15,000.00
1000R   20,000.00
1001E   25,000.00
1001R   30,000.00
1002E     7,500.00
1002R     1,000.00

 

 

Adjustments
JobTypeAmount
1000E      250.00
1000R      300.00
1000E      750.00
1000R      900.00
1002E   2,250.00
1002R   2,600.00
Need Help
  • Sean's avatar
    Sean
    10 years ago

    ntravis This should work... Let me know...

     

    Revenue =
CALCULATE ( SUM ( Estimate[Amount] ), Estimate[Type] = "R" )
    + CALCULATE ( SUM ( Adjustments[Amount] ), Adjustments[Type] = "R" )

     

     

5 Replies

    • ntravis's avatar
      ntravis
      Frequent Visitor
      10 years ago

      Hi Sean,

       

      Yes sorry, both of these tables are transaction tables that are related though a "Job Master" table, where there is one to many relationship that is flows in both directions.

       

      • Sean's avatar
        Sean
        Community Champion
        10 years ago

        ntravis This should work... Let me know...

         

        Revenue =
CALCULATE ( SUM ( Estimate[Amount] ), Estimate[Type] = "R" )
    + CALCULATE ( SUM ( Adjustments[Amount] ), Adjustments[Type] = "R" )

         

         

  • waltheed's avatar
    waltheed
    Impactful Individual
    10 years ago

    Hi ntravis,

     

    I assume that you create a relationship between the two tables. The filter function is what it makes go wrong. it first filters both tables for R, and then sums it up. 

     

    This should solve your issue:

    Add a calculated column to the estimates table.

    Use the LOOKUPVALUE function to find any related values in the adjustment table.

     

    Calculated column: Adj

    = LOOKUPVALUE(Adjustments[Amount]; Adjustments[Job]; Estimates[Job]; Adjustments[Type]; Estimates[type])

     

    Then use a simple

    Revenue: = Sum(Estimates[Amount]) + Sum(Estimates[Adj])

    to get your result.

     

    Another option would be to create an extra column, which is a combined key of Job and Type, in both tables and use that field to create a relationship. Then also a simple sum will work.