Circular dependency questions with measures

[analyticspbi87]

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.

[parry2k]

@analyticspbi87 glad to hear. Good luck!

[analyticspbi87]

Thank you so much! All the new measures really help me out and it definitely means I have lots to learn.

[parry2k · Accepted solution]

@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.