Converting Excel Formulas to DAX: SUMIF and VLOOKUP

Hi Jimmy, Thanks for the quick response! I worked through the different DAX formulas you provided, but was not able to get them to work as expected. 

(Table1)Current Revenue and Current GP:

I am not using a slicer - could you maybe walk me through how you would structure this? 

Current Revenue and Current GP values seem to sum the entire corresponding columns, and then add the Change in Revenue or Change in GP respectively.

I need row-level calculations, like this: Add Table1: Original GP to Table2:Change in GP if the corresponding SO# is found in Table3: S/O#. ALLSELECTED may be too broad an expression in this case, and instead I may need validation of SO#-to-SO#.

Here is an example of what the formula is actually doing to calculate Current Revenue (and Current GP) :

On Table 1, find S/O#: 11621607. (Observe that there is a positive value for Change in Revenue, and a negative value for Change in GP):

On Table 2, find the same S/O#.

Since it found the same S/O# on Table 2, it will add Table1:Change in Revenue + Table2:Original Revenue, thus resulting in the following Current Revenue value:

If there was no change in revenue, it would simply return the Original Revenue value since there was nothing added/subtracted. All of the above details also apply to Current GP. Note that each calculation is done at the row-level, so it will iterate through all rows with the same formula, but the input/output values should be different.

(Table2)Type:

The Type column is created to look up and return the Table1'[SALE TYPE] based on the corresponding Table3[S/O#] from that row. My guess is that the cardinality may not play well in this case, which is "1 to many" from "Table3 to Table 1" respectively. Both Table 1 and 2 have multiple possible values for Sale Type for each SO#. So basically when it looks up the SO# on Table1, I think DAX sees there are multiple SALE TYPE values possible for each SO#. Excel will conveniently pick one of the values, but I think PowerBI DAX gets caught up since there are multiple possible values. If anything, I would at least want to see the first Sale Type value if there happen to be multiple. A concatenation of both values, for example; Sale Type = MA / T, would also be acceptable. 

Your coding did bring to light several of the mistakes I was making with DAX, and I am researching other expressions now to see if I can create the validation I need. I am certain that this is much simpler than I thought.  I would be delighted if you wanted to continue working on this, and please let me know if you have any questions. Thank you for your help!

Alex