Struggling to understand circular reference

[masplin]

I have powerpivot model in Excel 2016 and can't understand why i'm getting a circular reference for this formula

=IF(    Total EBITDA>=3000 &&
    VALUE(Results[EBITDA])<=10000 &&
    VALUE(Results[ROC])>0.20 
           ,      "Y",
      BLANK()
      )

but not for this formula

=IF(    VALUE(Results[EBITDA])>=3000 &&
    VALUE(Results[EBITDA])<=10000 &&
    VALUE(Results[ROC])>0.20 
           ,      "Y",
      BLANK()
      )

Where in the first case "Total EBITDA" is a measure =SUM(Results[EBITDA]). why does VALUE on a table column work but what looks like the same measure not?

I have another calculation I'd like to add to the IF statement using

EBIDTA 3Y Growth:=IFERROR((([Total EBITDA]-[Total EBITDA_Y3])/ABS([Total EBITDA_Y3])),BLANK())

Where Total EBITDA_Y3:=SUM([EBITDA_Y3])

but adding EBITDA 3Y Growth>0 or VALUE([EBIDTA 3Y Growth])>0 both generate a circular error

The circular reference mentions this field which make no sense as cant see any connection

CompanyFind=IF(RELATED(DME[Company name])=BLANK(),IF([KeywordInstance]=0,"Reject","New"),"DME")

The table DME only has one relationship and

KeywordInstance:=SUMX(Keyword,
    SUMX(Results,
        Keyword[Weight]*
        (LEN(LOWER(Results[Trade description]))-LEN(SUBSTITUTE(LOWER(Results[Trade description]),LOWER(Keyword[Keyword]),"")))/
        LEN(Keyword[Keyword])
    )
)

where keyword has no relationships

Any advice appreciated as just can't work out where the issue is

 

[gazpage]

I would guess it's due to this:

https://www.sqlbi.com/articles/understanding-circular-dependencies/

Remember that when you use a measure in another measure the engine puts an implicit CALCULATE around it, ie in your measure, Total EBITDA is actually =CALCULATE( SUM(Results[EBITDA] ).

 

[masplin]

Ah brilliant all becomes clear. So if i add an ID column and make it the Row Identifier should work

Thanks

 

[gazpage]

I personally haven't done that, but otherwise you might need to use ALLEXCEPT in your measure, which is pretty tedious.

 

[masplin]

In excel works perfectly. i understand this cant be done in Power BI Desktop which must be a pain.