Creating a virtual table and joining back on

[abcttt233]

I have two tables in power QI

Table Plan

Table Risks

These two tables join together by id and it's a many to one relationship from Plan --> Risks.

I want to sum a field called Plan Net in Table Plan by a column Plan Name and attach that aggregated Value back down to each risk.

Example plan table looks like this:

Plan Table
Plan Name	Plan Net
A	1000
B	2000
A	3000

Result
ID	Plan Name	Plan Net
1	A	4000
2	B	2000
3	A	4000

I want the result table to look like this.

I am trying to do this in a measure:

PlanAggregated =

CALCULATE(

    [sum of Plan Net],

    ALLSELECTED(Risk[Id]))

However, this does not work and it seems to join on random IDs not the ones I would expect. I have also tried ALLEXCEPT which also doesnt work.

Is there a way to do this?

[ryan_mayu]

what is your risk table looks like?

so the result is only based on the plan table?