DIVIDE function with LOOKUPVALUE on measure

I have these 2 tables,

Project  Area
A1       5
A2       7
A3       4
B1       9
B2       6
B3       8

Project  Cost category total cost
A1       Z1            100
A1       Z2            200
A2       Z1            400
A3       Z1            300
A3       Z2            250
A3       Z3            300
B1       Z1            350
B1       Z2            200
B2       Z1            180
B2       Z2            320
B3       Z1            400
B3       Z3            200

Project, area, cost category and total cost

I was hoping to have a a cost per sqm for each project and per zone.

Output, grand total per project

Project   Grand total cost     cost per sqm
A1        300                  60
A2        400                  57
A3        850                  213
B1        550                  61
B2        500                  83
B3        600                  75

Output, grand total per zone
Project   Grand total cost     cost per sqm
A         1550                 97
B         1650                 72

I tried to have this measure

cost per sqm = 
DIVIDE(
    SUM('Table 2'[total cost]),
    LOOKUPVALUE('Table 1'[Area],'Table 1'[Project],'Table 2'[Project])
)

the idea is to sum the total cost / area for cost per sqm each project and sum of the total cost / total area for each zone

I tried to put SUM() on lookupvalue too but it doesnt work.

Solution

I created the total cost per project separately :

Total Cost per Project = SUM('Table2'[total cost])

Then for for the cost per sql per project :

Cost per sqm per Project = 
DIVIDE(
    [Total Cost per Project],
    LOOKUPVALUE('Table1'[Area], 'Table1'[Project], MAX('Table2'[Project]))
)

I used the LOOKUPVALUE yo fetch the Area for each Project from Table1. Then the MAX('Table2'[Project]) to make sure that the context is set to a specific project when the measure is calculated.

For the 2nd part :

Then I created a column to get only the alphabetic part of the Project :

Project Calculated = LEFT('Table1'[Project], 1)

I calculated the total area for each Project Calculated category (A, B, ...) :

Total Area = CALCULATE(SUM('Table1'[Area]), ALLEXCEPT('Table1', 'Table1'[Project Calculated]))

Then calculated the grand total cost for each Project Calculated category :

Grand Total Cost per Project Calculated = CALCULATE([Grand Total Cost], ALLEXCEPT('Table1', 'Table1'[Project Calculated]))

and finally :

Cost per Sqm = DIVIDE([Grand Total Cost per Project Calculated], [Total Area])