Forum Discussion

analyticspbi87's avatar
1 year ago
Solved

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:

BuildingYearValueType CostTypeGroup TOTAL Prop  TOTAL Rev Multiplier
A2021100Add YearRecoverableInclude        100,000.00      200,000.001.5
A2021100Amort YearNon-RecoverableExclude        100,000.00      200,000.001.5
A2021120Add YearNon-RecoverableInclude        100,000.00      200,000.001.5
A2022130Add YearRecoverableInclude        100,000.00      200,000.002.25
A2022145Add YearNon-RecoverableInclude        100,000.00      200,000.002.25
A2023124Add YearNon-RecoverableInclude        100,000.00      200,000.001.4
A2024125Add YearNon-RecoverableInclude        100,000.00      200,000.001.5
A2025170Add YearNon-RecoverableInclude        100,000.00      200,000.001.8
B2021220Add YearRecoverableExclude        600,000.00      400,000.001.5
B2021221Add YearNon-RecoverableInclude        600,000.00      400,000.001.5
B2022260Add YearNon-RecoverableInclude        600,000.00      400,000.002.25
B2023255Add YearNon-RecoverableInclude        600,000.00      400,000.001.4
B2024240Add YearNon-RecoverableInclude        600,000.00      400,000.001.5
B2025230Amort YearNon-RecoverableInclude        600,000.00      400,000.001.8
B2025230Add YearNon-RecoverableInclude        600,000.00      400,000.001.8

 

The Output data should be as follows:

  20212022202320242025
% of PropA0.15%0.12%0.09%0.08%0.09%
 B0.02%0.02%0.03%0.03%0.02%
% of RevA0.04%0.03%0.04%0.04%0.05%
 B0.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

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

     

     

     

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