How to use SUMPRODUCT (SUMX) in a calculated column for one related and one unrelated table? (Excel)

[JollyRoger01]

I have three tables and I can only relate two of them together due to circular references errors I get when trying to relate the other. I have tried to explain my goal as I've been writting this post but I think it is easier to simply show a desired output in a file.

I am looking for a formula to write in the Changes calculated column of Table 3 that gives me the desired output as shown in the worksheet. Please note this is a simplified version of my actual data and I can't change the relationship structure.

Book1 

[Fowmy · Accepted solution]

@JollyRoger01 

Add the following calculated column: I attached the file below my signature.

Changes = 
SUMX(
    FILTER(
        Resource,
        Resource[System] = EARLIER(System[System] )
    ),
    Resource[Changes] * 
    MAXX(
        FILTER(
            'Weight',
            'Weight'[Resource] = Resource[Resource] 
        ),
        'Weight'[Weighting]
    )
)

[JollyRoger01]

Worked like a charm, thank you! Out of interest, is this a common occurance and common solution?

[Jihwan_Kim]

Changes Measure : =
SUMX (
RELATEDTABLE ( Changes ),
Changes[Changes] * RELATED ( Resources[Weighting] )
)

https://www.dropbox.com/s/vyr52v7c4h9012r/jolly.pbix?dl=0 

[JollyRoger01]

Thank you, however as I pointed out I cannot use a relationship to the Resources table as you have outlined. My actual relationship tree is quite complicated and it doesn't allow for a relationship to that table, so I must call it another way.