Why new Column & Measure not working properly

[Sudhakar1000]

Hi All,

i have a two table (OINV, Sales),

Oinv is Invoice table and sales is multi table joined include OINV also

So now Sales table i have to Subtract between two column, when i am trying new calculate column its 

INV_Basic CALCULATE(SUM('sales 1'[OINV.DocTotal])-SUM('sales 1'[OINV.VatSum]))

i am getting below error

also i am trying to measure its working but table will reflecting complete values like line total

Table

Here INV Basic should be Total - Tax =165950 but my table coming more then 44923689.99.

Please help me how should resolve the issue

 

[Sudhakar1000 · Accepted solution]

Thank you for solution V-jianpeng, i have used the below measure its worked 

Bal Amt1 =
SUMX(
VALUES('sales 1'[OINV.DocEntry]),
CALCULATE(
MAX('sales 1'[OINV.DocTotal])-MAX('sales 1'[OINV.VatSum])
)
)

[Anonymous]

Hi, @Sudhakar1000 

According to the error shown in your image, your calculated column has a circular dependency, and your 'sales 1'[OINV. DocTotal], ‘sales 1’[OINV. VatSum] is also a calculated column? And the calculations depend on 'sales 1'[Cloumn 2], 'sales 1'[SOBasic in Lakh].
Circular dependencies are very common in Power BI, and the calculation result of column A depends on the calculation result of column B, and the calculation result of column B also depends on the calculation result of column A, which will cause Power BI to fail to determine the current calculation order, resulting in errors.
You can refer to the following community blog post that will help you understand circular dependencies and solve the current problem:

Circular Dependency between Calculated Columns in ... - Microsoft Fabric Community

Best Regards

Jianpeng Li

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

[Sudhakar1000 · Accepted solution]

Thank you for solution V-jianpeng, i have used the below measure its worked 

Bal Amt1 =
SUMX(
VALUES('sales 1'[OINV.DocEntry]),
CALCULATE(
MAX('sales 1'[OINV.DocTotal])-MAX('sales 1'[OINV.VatSum])
)
)

[Anonymous · Accepted solution]

Hi, @Sudhakar1000 

Thank you a lot for sharing the solution. You can mark helpful replies and solutions you share as solutions so that others in the community can quickly find answers to the same problem. Thank you again for your cooperation.

Best Regards

Jianpeng Li

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

[Anonymous]

Hi, @Sudhakar1000 

According to the error shown in your image, your calculated column has a circular dependency, and your 'sales 1'[OINV. DocTotal], ‘sales 1’[OINV. VatSum] is also a calculated column? And the calculations depend on 'sales 1'[Cloumn 2], 'sales 1'[SOBasic in Lakh].
Circular dependencies are very common in Power BI, and the calculation result of column A depends on the calculation result of column B, and the calculation result of column B also depends on the calculation result of column A, which will cause Power BI to fail to determine the current calculation order, resulting in errors.
You can refer to the following community blog post that will help you understand circular dependencies and solve the current problem:

Circular Dependency between Calculated Columns in ... - Microsoft Fabric Community

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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