Forum Discussion

P0ach3r1's avatar
P0ach3r1
Frequent Visitor
3 years ago
Solved

circular dependency - need help on formula

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 a...
Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    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's avatar
P0ach3r1
Frequent Visitor
3 years ago

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's avatar
Mikelytics
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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.