Create new column with calculate column and another table column value

[darkbear33]

Hi,

I create a column in Fac CA to know the turnover by product:
CAProduct =
IF (CALCULATE (
PRODUCTX('Fac CA', 'Fac CA'[Pourcent] * RELATED('Sites'[CA])))
= BLANK(), 0,
CALCULATE (
PRODUCTX('Fac CA', 'Fac CA'[Pourcent] * RELATED('Sites'[CA]))))

And after, I want to create another column to have the turnover by day:
CADay =
VAR _Nb = RELATED('Sites'[NbDay])
VAR _CA = [CAProduct]
RETURN DIVIDE(_CA, _Nb, 0)
=> Error: A circular dependency was detected: Fac CA[CADay], Fac CA[CAProduct], Fac CA[CADay].

I don't know why it's not work because the first calculate column work.

The link : "Sites" 1 - * "Fac CA"

Thanks in advance!

[darkbear33]

Thanks for your answer.

It's make the same error and my relationship is good (... for me).

I'll do it on the powerquery side, too bad.

[v-junyant-msft]

Hi @darkbear33 ,

To resolve this issue, let's ensure that the 'CAProduct' column does not depend on the 'CADay' column.
Please try use this DAX:

CADay = 
    VAR _Nb = RELATED('Sites'[NbDay])
    VAR _CA = CALCULATE(SUMX('Fac CA', 'Fac CA'[Pourcent] * RELATED('Sites'[CA])))
    RETURN DIVIDE(_CA, _Nb, 0)

And please ensure that the relationship between the 'Sites' and 'Fac CA' tables is set up correctly. The error message suggests that there might be an issue with the relationship that is causing the circular dependency.

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