locked
Circular dependency error RRS feed

  • Question

  • Hi,

    My objective is to allocate costs from Regions to States.  You may download the workbook from here.

    I have a 3 column table (named as Cost on the Cost worksheet) with Dates in column 1, Regions in column 2 and Amount in column 3.  This table depicts how much cost was incurred in each region in each month.  For now, I am sharing data for 1 month only - Feb 2017.  There are only 6 rows in this table.

    I also have another 4 column table (Allocation table on the Cost allocation worksheet) - Calendar month (which is nothing but a column of dates), Region, State and Rack cost driver for State.  In this table, the user can enter a number in the last column to forcibly allocate a fixed number to a particular State within a region.  To keep things simple, I have left this column blank for now.

    In the PowerPivot model, I have written the following calculated column formula in the Allocation table

    =CALCULATE(SUMX(SUMMARIZE(Allocation_table,Allocation_table[Calendar month],Allocation_table[Region],Allocation_table[State],"ABCD",MIN([Rack cost driver for State])),[ABCD]),FILTER(Allocation_table,[Region]=EARLIER([Region])&&[Calendar month]=EARLIER([Calendar month])))

    The purpose of this formula is to show me the total manual cost which belongs to that Region.  So in the Cost allocation, if I were to enter 100000 in C2:C95 (this is the range for State AP in region APTK), then I expect to see the figure of 100000 in all rows where the Region is APTK.  When I close the PowerPivot window and click on Data > Refresh All, I get a circular dependency error.  Why am I getting this error?

    Please help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Sunday, May 14, 2017 3:02 AM

Answers

  • Hi @Ashish,

    Have you resolved your issue? If it is, please mark the helpful reply as answer.

    Thanks,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 25, 2017 9:11 AM

All replies

  • There is so much to learn :-). The cause of this problem is quite complex I must blog about it one day. I suggest you read this article here. http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/ It doesn't explain the issue, but it explains why you shouldn't use calc columns unless there is a good reason. Try to solve this with a measure instead.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Sunday, May 14, 2017 9:02 AM
    Answerer
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Due to my lack of understanding, reformulated the objective thus:
    Allocate monthly Region Costs equally to the States of the Region.
    PQ does all the work, so PP has no calculated columns and only one simple measure.
    If allocation has a different, fixed pattern, define pattern.
    http://www.mediafire.com/file/5p9gcsvxj4uxjvv/05_14_17a.xlsx

    Monday, May 15, 2017 3:39 AM
  • Hi Ashish,

    Why you create a new table using the formula.

    New Table=SUMMARIZE(Allocation_table,Allocation_table[Calendar month],Allocation_table[Region],Allocation_table[State],"ABCD",MIN([Rack cost driver for State]))

    Then create the calculated column using the formula, and check if it works fine.

    =CALCULATE(SUM([ABCD]),FILTER(New Table,[Region]=EARLIER([Region])&&[Calendar month]=EARLIER([Calendar month])))

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 15, 2017 6:43 AM
  • Sure.  Thank you for replying.  I will try to solve my question by using a measure.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Tuesday, May 16, 2017 11:54 PM
  • Hi Herbert,

    Thank you for sharing this workbook.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Tuesday, May 16, 2017 11:55 PM
  • Hi,

    Thank you for sharing this.  I will try this.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Tuesday, May 16, 2017 11:56 PM
  • Hi @Ashish,

    Have you resolved your issue? If it is, please mark the helpful reply as answer.

    Thanks,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 25, 2017 9:11 AM