Forum Discussion
Circular dependency questions with measures
Currently having an issue where 2 measures are creating a circular dependency.
I have a feeling it's because one of the calculations invole a filter. I tried using both measures (where te circular dependency is happening, as well as filters)
Sample data is as follows:
| Building | Year | Value | Type Cost | Type | Group | TOTAL Prop | TOTAL Rev | Multiplier |
| A | 2021 | 100 | Add Year | Recoverable | Include | 100,000.00 | 200,000.00 | 1.5 |
| A | 2021 | 100 | Amort Year | Non-Recoverable | Exclude | 100,000.00 | 200,000.00 | 1.5 |
| A | 2021 | 120 | Add Year | Non-Recoverable | Include | 100,000.00 | 200,000.00 | 1.5 |
| A | 2022 | 130 | Add Year | Recoverable | Include | 100,000.00 | 200,000.00 | 2.25 |
| A | 2022 | 145 | Add Year | Non-Recoverable | Include | 100,000.00 | 200,000.00 | 2.25 |
| A | 2023 | 124 | Add Year | Non-Recoverable | Include | 100,000.00 | 200,000.00 | 1.4 |
| A | 2024 | 125 | Add Year | Non-Recoverable | Include | 100,000.00 | 200,000.00 | 1.5 |
| A | 2025 | 170 | Add Year | Non-Recoverable | Include | 100,000.00 | 200,000.00 | 1.8 |
| B | 2021 | 220 | Add Year | Recoverable | Exclude | 600,000.00 | 400,000.00 | 1.5 |
| B | 2021 | 221 | Add Year | Non-Recoverable | Include | 600,000.00 | 400,000.00 | 1.5 |
| B | 2022 | 260 | Add Year | Non-Recoverable | Include | 600,000.00 | 400,000.00 | 2.25 |
| B | 2023 | 255 | Add Year | Non-Recoverable | Include | 600,000.00 | 400,000.00 | 1.4 |
| B | 2024 | 240 | Add Year | Non-Recoverable | Include | 600,000.00 | 400,000.00 | 1.5 |
| B | 2025 | 230 | Amort Year | Non-Recoverable | Include | 600,000.00 | 400,000.00 | 1.8 |
| B | 2025 | 230 | Add Year | Non-Recoverable | Include | 600,000.00 | 400,000.00 | 1.8 |
The Output data should be as follows:
| 2021 | 2022 | 2023 | 2024 | 2025 | ||
| % of Prop | A | 0.15% | 0.12% | 0.09% | 0.08% | 0.09% |
| B | 0.02% | 0.02% | 0.03% | 0.03% | 0.02% | |
| % of Rev | A | 0.04% | 0.03% | 0.04% | 0.04% | 0.05% |
| B | 0.04% | 0.03% | 0.05% | 0.04% | 0.03% |
Formula wise in PowerBI
I used the measures
% of Prop =
DIVIDE(
(DIVIDE(
CALCULATE(
SUM('Input'[Value]),
'Input'[Group]<>"Exclude",
'Input'[Type Cost]="Add Year"
),
1000
))
(DIVIDE(
(SUM(Data'[Multiplier]) * SUM('Data'[TOTAL Prop])
),
1000)
))
)
% of Rev =
DIVIDE(
(DIVIDE(
CALCULATE(
SUM('Input'[Value]),
'Input'[Group]<>"Exclude",
'Input'[Type Cost]="Add Year",
'Input'[Type]="Non-Recoverable"
),
1000
)),
(DIVIDE(
(SUM('Input'[Multiplier])*SUM(Input[TOTAL Rev]) ),
1000)),
)
I have uploaded the files onto dropbox.
analyticspbi87 hey I took data from dropbox and created measures to break down the calculation and it seems to be working. Not sure why you are getting dependency error, maybe in your model you have more tables/calculated columns that is leading to the error.
See attached. It is always recommended to add seperate measure for complex calculations or use variable so that it is easy to debug.
Here is the output that in-align with excel sheet you provided.
3 Replies
- parry2k
Super User
analyticspbi87 hey I took data from dropbox and created measures to break down the calculation and it seems to be working. Not sure why you are getting dependency error, maybe in your model you have more tables/calculated columns that is leading to the error.
See attached. It is always recommended to add seperate measure for complex calculations or use variable so that it is easy to debug.
Here is the output that in-align with excel sheet you provided. - analyticspbi87
Helper I
Thank you so much! All the new measures really help me out and it definitely means I have lots to learn.
- parry2k
Super User
analyticspbi87 glad to hear. Good luck!