Need Assistance on Error about Circular Dependency

[tksnota]

Hi,

I managed to create a column for variance using the dax below

Variance = CALCULATE(SUM(GRV_SalesItems[Sales Price])) - CALCULATE(SUM(GRV_SalesItems[Purchase Price]))

 But when I tried to use a dax as shown below the Circular Dependency Error appears

The same Circular Dependency is experienced when I tried to create a dax using If statement so I can create a slicer for variance classified as negative, positive and zero.

Hope someone can help.

//NT

[v-jiewu-msft]

Hi @tksnota ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the new column to calculate the Variance.

Variance = CALCULATE(SUM('Table'[Sales Price])) - CALCULATE(SUM('Table'[Purchase Price]))

3.Create the new measure to calculate Margin instead of calculated column.

Margin01 = CALCULATE(SUM('Table'[Variance]) / SUM('Table'[Purchase Price]))

4.The result is shown below.

Best Regards,

Wisdom Wu

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

[tksnota]

Hi,

Is it possible to make the variance as columns instead of measure so I can add it into a slicer?

Plus how can i make a slicer to classify the variance based on negative, positive and zero because I tried using the if function but again encountered circular dependency

//NT

//NT

[v-jiewu-msft · Accepted solution]

Hi @tksnota ,

Please try the following formula:

Variance = ([Sales Price] - [Purchase Price]) / [Purchase Price]

Create the new column to classify the variance.

Variance classification = 
IF([Variance] > 0, "Positive", 
    IF([Variance] < 0, "Negative", "Zero")
)

Drag the date column into the slicer visual.

Best Regards,

Wisdom Wu

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

[DaleT]

Hi,

This blog explains very well about this issue and also it provides the solution. Please refer to it. https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/ 

Dale

[rubayatyasmin]

Hi @tksnota 

Did you try using separate measures to classify variance? something like below,

Variance Classification =
VAR CurrentVariance = [Variance]
RETURN

see if these help:
Solved: Circular dependancy error - Microsoft Fabric Community

Solved: Circular dependency - Microsoft Fabric Community

Solved: Circular dependency.....why??? - Microsoft Fabric Community

Solved: Error: circular dependency - Microsoft Fabric Community
IF(
CurrentVariance > 0, "Positive",
IF(CurrentVariance < 0, "Negative", "Zero")
)

[tksnota]

Hi,

I used your dax but still encountered a circular dependency as shown. Any possible option to fix my problem?