circular dependency - need help on formula

[P0ach3r1]

Hi,

I'm trying to write a formula in a column and am getting circular dependency error.  I want it to return red, amber or green depending on value in abs_Variance column.  I'm using abs to get absolute value as variance can be positive or negative.

The RAG formula is below and is giving me a circular dependency error due to the calculated column abs_variance:

RAG = if('Data Export (2)'[Abs_Variance]<0.05,"Green",if('Data Export (2)'[Abs_Variance]<0.01,"Amber","Red"))

the column for abs_variance is below:  

Abs_Variance = abs(IFERROR(DIVIDE(calculate(sum('Data Export (2)'[Actual/Projected]))-(calculate(sum('Data Export (2)'[Client Forecast]))), 'Data Export (2)'[Actual/Projected]),0))

I know I am meant to use Allexcept - I just don't know how or where it's meant to go.

Thanks in advance

[grantsamborn]

Hi @P0ach3r1 

I'm sorry that @Mikelytics and I got side-tracked.

Can you possibly share some fictitious data to work with?

[Mikelytics]

yea, the typical scenario would be division by zero or divison by blank.

But the zero in the 3rd argument came from you (which is totally correct) and not from me if you look in your formula. 🙂 I only simplified formula. Bu to know whether I am right I would need some feedback to the result.

Is your problem solved? Can you please show what happens if you use them?

Best regards

Michael 

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[grantsamborn]

Hi Michael

I think you are mistaking me for P0ach3r1.  This isn't my thread.  I was simply asking if DIVIDE's 3rd parameter would work.

[Mikelytics]

@grantsamborn ups 😄 You are right, sorry for the misunderstanding. Thats what confused me. Thank you highlighting.

But to give you more background. The third parameteter defines the value which will be returned if the division creates an error. The typical case is indeed having zero or blank() in the second parameter. But there can also cases like when you use a measure in the first parameter and the measure itself returnes an error. SO you would have ERROR divided by something which returns again an ERROR

In this scenario P0ach3r1 used 0 as the thrid parameter. If you do not provide a third parameter the default behaviour is returning BLANK()

Here you can find more information

DIVIDE function (DAX) - DAX | Microsoft Learn

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[grantsamborn]

if you take a look at his original "Abs_Variance" after formatting, you'll see he wasn't using the 3rd parameter of DIVIDE but instead using the default of IFERROR.

Abs_Variance =
ABS(
    IFERROR(
        DIVIDE(
            CALCULATE(
                SUM( 'Data Export (2)'[Actual/Projected] )
            )
                - (
                    CALCULATE(
                        SUM( 'Data Export (2)'[Client Forecast] )
                    )
                ),
            'Data Export (2)'[Actual/Projected]
        ),
        0
    )
)

daxformatter.com makes life easier.

[Mikelytics · Accepted solution]

Hi @P0ach3r1 

Can we maybe first try to simplify the formulas? I do not see the tables but I think the formula could also be written like this.

I assume that you crete the calcualted column in the same table where you also store all the columns you refer to, right?

Please build first this column

Abs_Variance =
ABS (
    IFERROR (
        DIVIDE (
           [Actual/Projected] - [Client Forecast],
           [Actual/Projected]
        ),
        0
    )
)

and then this

RAG =
IF (
    [Abs_Variance] < 0.05,
    "Green",
    IF (
        [Abs_Variance] < 0.01,
        "Amber",
        "Red"
    )
)

Please tell me what happens.

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[P0ach3r1]

Hi Mikelytics  - Thanks for the Help - just tried it and looks like worked, I will test the data to confirm fully. 

So - I'm an excel user and very new to PowerBI - hence the excel type formula. 

Moving forward - I don't need to use the table name in my formula if all fields I'm using are within the formula are in the current table? 

And your solution worked because we could amend the abs_variance formula to remove the calculate command?

[Mikelytics]

Hi @P0ach3r1 ,

Awesome and thank you for your feedback! Happy to support you on your Power BI journey! 🙂

To your question. Exactly, when you create a calculated column in a table and refer to the same table then you do not need to provide the table name. Only when you refer to other tables. But refering to other tables often you would need additonal formulas like RELATED() to get useful results.

BUT please be aware that in general calculated columns should not be your first choice when doing calculations in Power BI, there are several reasons starting looking on storage, performance and flexbility. If you need an additional column very often it is better to calculate the column in Power Query as a custom column upfront. On the other side start early to work with Measures which are more flexible. 

Typically (with some exception) you are better wih Measures or Power QUery Custom Columns. Most of the advanced techniques and recommenations from long experienced users do not include Calculated Columns. SO my advide to you is to invest more time as early as possible into Measures and Power Query.

Hope this helps you a little bit on your journey. 🙂

Best regards

Michael

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[grantsamborn]

Wouldn't using DIVIDE's 3rd parameter accomplish the same thing?

Abs_Variance =
ABS (
    DIVIDE (
       [Actual/Projected] - [Client Forecast],
       [Actual/Projected],
	   0
    ),
)

[Mikelytics]

the same like what? I only rewrited your formula. Could you please show the results and say whether it goes in the right direction? 🙂

[grantsamborn]

The 3rd argument safely handles division by zero.  Is there some other type of error that you are trying to handle?

(I'm only asking this to confirm whether or not what I've learned is correct.)