Power pivot question

[cmcrews]

I have a power pivot question. It seems really simple but I can not figure it out for the life of me. I have 2 tables one for investment cost and the other the investment fair market value. Both contain the general ledger code as the relationship. There is a many to one relationship for the investment system as the investment is broken down into several investors. I need to have the cost compared to the fair market value and determine if an adjustment needs to be make. The screenshot is a simple version of my data. I've figured out how to pull the cost and fmv into a pivot table but getting the adjustment is escaping me. The 3rd table is what I would need the data/pivot table to return. I guess I could do a vlookup but was looking for something better. Anything that I try is returning errors. Any and all help would be greatly appreciated!!!

 

[sandy666]

another approach with Power Query

let
    Tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Merge = Table.NestedJoin(Tbl1,{"Code"},Tbl2,{"Ycode"},"Tbl2",JoinKind.LeftOuter),
    FMVList = Table.AddColumn(Merge, "FMV", each [Tbl2][FMV]),
    Expand = Table.ExpandListColumn(FMVList, "FMV"),
    TSC = Table.SelectColumns(Expand,{"Code", "Cost", "FMV"}),
    Group = Table.Group(TSC, {"Code", "Cost"}, {{"FMV", each List.Sum([FMV]), type number}}),
    Adj = Table.AddColumn(Group, "Adjustment", each [Cost] - [FMV], type number),
    Type = Table.TransformColumnTypes(Adj,{{"Code", type text}, {"Cost", type number}})
in
    Type

 

[sandy666]

corrected picture

 

[cmcrews]

Took me a little to match the code you put in above with the actual steps in Excel but I finally figured it out! The query works much better as I have other data that's in the source tables that I ultimately need in the query as well. Thanks for the suggestion and solution!

 

[sandy666]

You are welcome
btw. solution was tailored to example you posted