Circular Dependency

[baronraghu]

Hi All,

I am having an issue

I have a list of sales data for three years

2016,2015,2014 and i would like to classify the companies as "Large",Medium" and "Small"

Classification criteria is

Lage sale over 400

Medium between 250 and 399

Small<250

A	2016	Bread	72	Pac
A	2015	Bread	449	Pac
A	2014	Bread	121	Pac
B	2016	Bread	272	Pac
B	2015	Bread	304	Pac
B	2014	Bread	319	Pac
Z	2016	Bread	50	Pac
Z	2015	Bread	196	Pac
Z	2014	Bread	219	Pac
Q	2016	Bread	257	Pac
Q	2015	Bread	184	Pac
Q	2014	Bread	349	Pac
W	2016	Bread	433	Pac
W	2015	Bread	406	Pac
W	2014	Bread	265	Pac
E	2016	Bread	189	Pac
E	2015	Bread	40	Pac
E	2014	Bread	411	Pac
R	2016	Bread	376	Pac
R	2015	Bread	409	Pac
R	2014	Bread	482	Pac
T	2016	Bread	198	Pac
T	2015	Bread	385	Pac
T	2014	Bread	308	Pac
Y	2016	Bread	161	Pac
Y	2015	Bread	395	Pac
Y	2014	Bread	131	Pac
U	2016	Bread	359	Pac
U	2015	Bread	434	Pac
U	2014	Bread	387	Pac
G	2016	Bread	459	Pac
G	2015	Bread	488	Pac
G	2014	Bread	278	Pac
I	2016	Bread	262	Pac
I	2015	Bread	303	Pac
I	2014	Bread	247	Pac

Note companies A,G and I are by default "Very Large "

What I have done so far is I created a new table VeryLarge to classify A,G and I into "Very Large "

Company Name	Company Size
A	Very Large
G	Very Large
I	Very Large

In the data model i had classified all the companies (including A, G and I) into Large,Small and Medium using If condition

Company Size:=if(CALCULATE([Total],OR(OR(Table3[Year]=2016,Table3[Year]=2015),Table3[Year]=2014))>=400,"Large",if(AND(CALCULATE([Total],OR(OR(Table3[Year]=2016,Table3[Year]=2015),Table3[Year]=2014))>249,CALCULATE([Total],OR(OR(Table3[Year]=2016,Table3[Year]=2015),Table3[Year]=2014))<400),"Medium","Small"))

Then, I created a new column as Revised Company Size and I used this formula

Revised Company Size:=if(isblank(related('veryLarge'[Company Size])),[Company Size],"Very Large")

This is showing me a circular error on [Company Size].

Could anyone please help me on this

Thanks

[mattbrice]

Though i am not sure why you are including a filter for the year if the table only contains those years, you can do the whole thing in one Calculated Column:

=
VAR VeryBigCompany =
    RELATED ( 'very large'[Company] )
VAR MyTotal =
    CALCULATE ( [Total], Table3[Year] IN { 2014, 2015, 2016 } )
RETURN
    IF (
        Table3[Company] = VeryBigCompany,
        "Very Large",
        SWITCH (
            TRUE (),
            MyTotal < 250, "Small",
            MyTotal >= 250
                && MyTotal < 400, "Medium",
            "Large"
        )
    )

And unless [Total] calculates the total for the company across all rows, this column labels on a per row basis.  Company X could have rows in all 3 (or 4 ) categories.  

[baronraghu]

Thanks mattbrice

Sorry for delay in my reply.

The reasom for filtering the year is that based on the total sale made each year the companys classification will change. Like if company A made a tota sale of 1000 in 2014 it will be a very large company but if in 2015 it made a sale of just 275 it is a Medium company. But since A is by default a very large company the sales  volume will not impact is classification

Hope this helps

[v-huizhn-msft]

Hi @baronraghu,

Have you resolved your problem? Please mark corresponding reply as answer, which will help other people find the solution easily.

Best Regards,
Angelia