Excel user sumif problem in DAX

[ntravis]

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
Job	Type	Amount
1000	E	15,000.00
1000	R	20,000.00
1001	E	25,000.00
1001	R	30,000.00
1002	E	7,500.00
1002	R	1,000.00

Adjustments
Job	Type	Amount
1000	E	250.00
1000	R	300.00
1000	E	750.00
1000	R	900.00
1002	E	2,250.00
1002	R	2,600.00

[waltheed]

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.

[Sean]

@ntravis Are these 2 tables related in any way?

[ntravis]

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 · Accepted solution]

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

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

 

[ntravis]

Thanks Sean!