Matching data in two unrelated tables for a SUM

[Anonymous]

Hi

I hope you can help!

I have three tables;

DISREPAIR DATA  is a list of properties where a claim has been raised because the property is in disrepair and it contains a property reference.

REPAIR FOLLOWING DISREPAIR is the other table is where a repair has been raised following a disrepair claim, it also contains a property reference.

PROPERTY DATA third table is the property table with a property reference.

DISREPAIR DATA and REPAIR FOLLOWING DISREPAIR are connected to PROPERTY DATA using one-to-many relationships.

I want to calculate the cost of REPAIR FOLLOWING DISREPAIR (there is a repair cost column) ensuring that only property references which appear in both DISREPAIR DATA and REPAIR FOLLOWING DISREPAIR are calculated.

How would I do this? I thought it would be either relatedtable or related function but I can't get it to work.

Thanks in advance! 

[Anonymous]

Hi

I hope the below is ok!

So I want to work out the repair amount in the Disrepair Following Table but only if the property reference in Disrepair Following Repair appears in the Disrepair Data table @amitchandak 

[Anonymous]

@smpa01 I owe you a pint plus I've learnt something knew too which I've been trying to learn, thank you!!!

[smpa01 · Accepted solution]

@Anonymous  you can try out a measure like this

Measure =
CALCULATE (
    SUM ( DisrepairFollowingRepair[RepairAmount] ),
    TREATAS (
        VALUES ( DisrepairData[Property Reference] ),
        DisrepairFollowingRepair[Property Reference]
    )
)

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.