Need help with circular dependency in power bi

[tushar479]

Hello, I am trying to replicate excel formulaes for Base and Running total in Power BI using measures, but, i am getting circular dependency error

SEQ_NO	NEW_GROUP_IND	RECOMMEND_IND	Total_Requested	BASE	%_OF_REQUEST	BASE_ALLOC	PARTIAL_COUNT	RUNNING_PARTIAL_COUNT	RUNNING_TOTAL	BASE_BUDGET	Total Base
1	1	1	$                      100,000	90000	0.9	90000	0	0	90000	980000	90000
2	0	1	$                                 60,000	54000	0.9	54000	0	0	144000	980000	54000
3	0	1	$                                 60,000	54000	0.9	54000	0	0	198000	980000	54000
4	0	1	$                               100,000	90000	0.9	90000	0	0	288000	980000	90000
5	0	1	$                                 60,000	54000	0.9	54000	0	0	342000	980000	54000
6	0	1	$                               100,000	90000	0.9	90000	0	0	432000	980000	90000
7	0	1	$                               100,000	90000	0.9	90000	0	0	522000	980000	90000
8	0	1	$                                 65,000	58500	0.9	58500	0	0	580500	980000	58500
9	0	1	$                                 69,500	62550	0.9	62550	0	0	643050	980000	62550
10	0	1	$                                 60,000	54000	0.9	54000	0	0	697050	980000	54000
11	0	1	$                               100,000	90000	0.9	90000	0	0	787050	980000	90000
12	0	1	$                                 76,212	68590.8	0.9	68590	0	0	855640	980000	68590
13	0	1	$                                 60,000	54000	0.9	54000	0	0	909640	980000	54000
14	0	1	$                                 60,000	54000	0.9	54000	0	0	963640	980000	54000
15	0	1	$                                 60,000	16360	0.272666667	0	1	1	963640	980000	0

Below are the formulaes:

Base =IF([RECOMMEND_IND]=1,IF(NEW_GROUP_IND=1,IF(([Total_Requested]*[Selected_value_in_Parameter])<=[BASE_BUDGET],([Total_Requested]*[Selected_value_in_Parameter]),[BASE_BUDGET]),IF([RUNNING_TOTAL(previous row value)]+([Total_Requested]*[Selected_value_in_Parameter])<=[BASE_BUDGET (previous value)],([Total_Requested]*[Selected_value_in_Parameter]),[BASE_BUDGET]-[RUNNING_TOTAL(previous row value)])),0)

Running_Total = IF(NEW_GROUP_IND=1,[BASE_ALLOC],[RUNNING_TOTAL(previous row value)]+[BASE_ALLOC])

BASE_ALLOC = FLOOR(IF(NEW_GROUP_IND=1,IF([%_OF_REQUEST]>=[Selected_value_in_Parameter],Base,0),IF([RUNNING_PARTIAL_COUNT(Previous row value)]=0,IF([%_OF_REQUEST]>=[Selected_value_in_Parameter],Base,0),0)),10)

[%_OF_REQUEST] =IF(Base>0,Base/Total_Requested,0)

PARTIAL_COUNT =IF([%_OF_REQUEST]>0,IF([%_OF_REQUEST]<[Selected_value_in_Parameter],1,0),0)

RUNNING_PARTIAL_COUNT =IF(NEW_GROUP_IND=1,PARTIAL_COUNT,[RUNNING_TOTAL(previous row value)]+PARTIAL_COUNT)

[raishubham955]

To resolve this issue use CALCULATE + FILTER to fetch previous row values dynamically. Instead of direct recursion, SUMX, CALCULATE, and variables can help in creating sequential calculations.

• Base Calculation:

  • If it's a new group, allocate either the requested amount or available budget.
  • If not a new group, use the previous running total to determine allocation.

• Running Total:

  • Start fresh for new groups.
  • Add the previous row’s Running Total and the current Base Alloc.

• Base Allocation:

  • If the % of Request meets the threshold, allocate the Base amount.
  • Use FLOOR() to round to the nearest 10.

• % of Request Calculation:

  • If Base is greater than zero, divide by Total Requested.

• Partial Count:

  • Mark as 1 if % of Request is below the selected threshold.

• Running Partial Count:

  • Start fresh for a new group; otherwise, add previous row’s count.

[Anonymous]

Hi @tushar479 

If I understand correctly, you now need to calculate Base, Running_Total, BASE_ALLOC, [%_OF_REQUEST], PARTIAL_COUNT, RUNNING_PARTIAL_COUNT and expect the results to be as shown in the corresponding columns in the table? 

If so, according to my observation, you used [RUNNING_TOTAL(previous row value)] in the Base formula, and [BASE_ALLOC] in the Running_Total formula, then you used Base in the BASE_ALLOC formula. The formulas depend on each other, which should be the reason for the error. Just like in the following test, Measure2 was used in Measure, and Measure2 was used in Measure, so an error occurred.

In order to help you better, first I need to confirm with you which columns are already in the original data? Which column is used by [Selected_value_in_Parameter]? Could you please tell me what effect you want to achieve with Power BI?

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tushar479]

Hello,

yes, that's correct. I want to calculate the above mentioned formulaes in PowerBI.

I have created a filter "[Selected_value_in_Parameter]" that has values 1-100 which is used in the formulaes calculation.

The below values are static and present in the table already.

SEQ_NO

, NEW_GROUP_IND

, RECOMMEND_IND

, Total_Requested