I have the following data structure:

• Table “Plan” which joins to table “Planning” on PlanKey 1 to many relationship single direction

• Table “PlanningClass” which joins to “Planning” On PlanClassKey, 1 to many single direction

• Table “PlanningClass” also joins to table “Amounts” on PlanClassKey, 1 to many, single direction

• Table “Amounts” joins to table “Risk” on RiskID on a many to 1 relationship, both direction

• Table “Risk” joins also to “Planning” on RiskID as a 1 to many relationshiop, single direction.

I want to create a measure which sums another measure location on the “Planning” table grouped up by PlanningClassName (a column in the Planning Class Table. This measure should then show the aggregated value for each plan class name to a table which has multiple columns in the Risk table (whose unique ID is riskID for each row).

Here’s what I have:

PlanAmountAggregated =
CALCULATE(
    [Sum of Plan Amount],
    'Plan'[Plan Basis] = "abc",
    ALLEXCEPT('Planning Class', 'Planning Class'[Planning Class Name]))

this shows the correct amount in a simple table of planning class name & amount; however if I then add in RiskID it doesn’t show the correct amount for each risk id; despite there being a Planning Class Name for each risk ID which comes from the link to the Amounts table and then back to the Risk table.

I have tried many things but nothing seems to work. How do I do this?