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.

     

Mikelytics's avatar
Mikelytics
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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's avatar
grantsamborn
Icon for Solution Sage rankSolution Sage
3 years ago

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

    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's avatar
      grantsamborn
      Icon for Solution Sage rankSolution Sage
      3 years ago

      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.