Forum Discussion

baronraghu's avatar
baronraghu
Helper III
9 years ago

Circular Dependency

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

 

A2016Bread72Pac
A2015Bread449Pac
A2014Bread121Pac
B2016Bread272Pac
B2015Bread304Pac
B2014Bread319Pac
Z2016Bread50Pac
Z2015Bread196Pac
Z2014Bread219Pac
Q2016Bread257Pac
Q2015Bread184Pac
Q2014Bread349Pac
W2016Bread433Pac
W2015Bread406Pac
W2014Bread265Pac
E2016Bread189Pac
E2015Bread40Pac
E2014Bread411Pac
R2016Bread376Pac
R2015Bread409Pac
R2014Bread482Pac
T2016Bread198Pac
T2015Bread385Pac
T2014Bread308Pac
Y2016Bread161Pac
Y2015Bread395Pac
Y2014Bread131Pac
U2016Bread359Pac
U2015Bread434Pac
U2014Bread387Pac
G2016Bread459Pac
G2015Bread488Pac
G2014Bread278Pac
I2016Bread262Pac
I2015Bread303Pac
I2014Bread247Pac

 

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 NameCompany Size
AVery Large
GVery Large
IVery 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

 

 

Need Help

3 Replies

  • mattbrice's avatar
    mattbrice
    Solution Sage
    9 years ago

    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.  

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      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

    • baronraghu's avatar
      baronraghu
      Helper III
      9 years ago

      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