Forum Discussion
Creating a virtual table and joining back on
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?
1 Reply
what is your risk table looks like?
so the result is only based on the plan table?
