Circular Dependency while creating multiple calculated columns

[ajay_techbrawn]

Hello experts,

I am able to calculate formula for sum of average of Actual Amount field and it was working fine. I tried to create the formula in the same way for variance and over variance fields but facing circular dependancy error.

Please find the below link for sample file for your reference. 

Sample.pbix - Google Drive 

Regards
Ajay

[v-yetao1-msft · Accepted solution]

Hi @ajay_techbrawn 

Power BI does not allow two calculated columns that contain measures that are also based on the table .

In some scenarios, the DAX engine cannot build the dependency diagram. If two objects depend on each other, the engine does not know which one to compute first. If A depends on B and – at the same time – B depends on A, then it is impossible to compute A nor B. As soon as you compute A, this triggers the refresh of B. So you start refreshing B, wich in turn triggers the refresh of A. You are stuck in an infinite loop with no hope of ever getting the job done.

This situation, an infinite loop in the query plan, triggers the infamous circular dependency error. The only way to get rid of the problem is to avoid any circular dependencies. 

The following link can help you better understand circular dependency .

https://www.sqlbi.com/articles/understanding-circular-dependencies/ 

So you need change the behavior of the context transition by introducing ALLEXCEPT. When using ALLEXCEPT you can remove columns from the context transition, thus keeping in the dependency list only the required columns.

I made a little change to your second and third formulas .

Formula (Sum of Avg Over Variance) = CALCULATE(SUMX(VALUES(Sheet1[Plant]),[Avg Over Variance]),ALLEXCEPT(Sheet1,Sheet1[Plant],Sheet1[Material Group]))

Formula (Sum of Avg Variance) = CALCULATE(sumx(VALUES('Sheet1'[Plant]), 'Sheet1'[Avg Variance]),ALLEXCEPT(Sheet1,Sheet1[Plant],Sheet1[Material Group]))

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[amitchandak]

@ajay_techbrawn , you are trying to measure in a column. That will not work. You have create a measure or create a column using columns only

example measure

calculate(sumx(VALUES('Sheet1'[Plant]), 'Sheet1'[Avg Variance]), allselected()) 

[ajay_techbrawn]

Hi @amitchandak ,

I have tried the formula given by you but it is not working as per my requirement. 

I have 3 fields Actual Amount, Variance and Over Variance. 
First I have to calculate average of Actual Amount with respect to material wise and I am able to do that using measure.

Avg Actual Amount = CALCULATE(AVERAGE(Sheet1[Actual Amount]), FILTER(ALLSELECTED(Sheet1), Sheet1[Plant] = MAX(Sheet1[Plant]) && Sheet1[Material Group] = MAX(Sheet1[Material Group])))

Then I have to display the subtotals of that average field as SUM. I am able to do that also for Actual Amount using a column as shown below in the last column.

Formula (Sum of Avg Actual Amount) = sumx(VALUES('Sheet1'[Plant]), 'Sheet1'[Avg Actual Amount])

Till here it was good!

In the similar way, I have to calculate average and Sum of average for Variance and Over Variance fields.
I am able to calculate average using measures but not able to create Sum of average using columns. 
I am facing Circular dependency while calculating the sum of average. 

How can I solve this issue? Could you please do and show.

Regards
Ajay

[ajay_techbrawn]

Hello experts,

Could anyone please look into the issue and provide your inputs to resolve.!

Regards

Ajay