# Thread: Calculated Column Circular Error need alternative solution Thanks: 0 Likes: 0

1. ## Calculated Column Circular Error need alternative solution

Hello,

I unfortunately/fortunately learned the hard way yesterday that using the calculate function in calculated columns doesn't always work out so well and have since spent entirely too much time spinning my wheels trying to figure out an alternative solution to my report requirement. Hoping someone can help point me in the right direction from here.

Simplified version of what I'm doing here (4 tables):

Two data tables as follows (accounts receivable balance current week and prior week)

 Company Customer Parent Customer A/R Balance

Fact table connecting the two A/R tables derived using power query using concatenated [Company]&[Customer]&[Parent Customer] fields, and eliminating duplicates, and adding same field to end of A/R tables. Called Master_CoCP (company+Customer+Parent Cust)

Last table is a Customer Table

 Customer Unit

Now here's where I'm getting stuck. Ultimately, what I'm trying to do is generate a pivot table that looks like the following, that groups customers based on whether they are currently in a top ten list, or whether they were and fell off that list in the subsequent report:

 Parent Customer Current Rank Current Balance Prior Rank Prior Balance Top Ten Parent Customers Customer A Customer B... Formerly Top Ten Parent Customers Customer X

To achieve this, I used lookup values to bring in the Customer[Unit] to my master fact table Master_CoCP, then wrote the following calculated columns into that same table:

Customer Balance Current = Calculate(sum(Aging_Current[Balance]),allexcept(Master_CoCP,Master_CoCP[Parent Customer],Master_CoCP[Unit]))

Customer Rank Current = rankx(CALCULATETABLE(Master_CoCP,allexcept(Master_CoCP,Master_CoCP[Unit])),[Customer Balance Current],,,DENSE)

Repeated for prior aging report then wrote a simple if statement in another column to say if <10 current, > 10 prior.

This worked beautifully (maybe not elegant) until such time as I loaded new Aging Reports and tried refreshing all queries and connections.... learn something new every day I guess.

Sorry if this is too long and doesn't make sense. I thought the length of my post should follow suit with how long I've been beating my head on the desk on this one.

Any ideas would be much appreciated. Thanks in advance!

2. ## Re: Calculated Column Circular Error need alternative solution

Hoping to bump this one, as I'm still struggling to find a solution. Maybe it will help if I put my problem a different way:

My desired end pivot table is a list of customers with their current balance and prior balance (from two receivables aging reports). I have created two measures to rank my customers: Top Ten for current period and Top Ten for prior period.

What I'm trying to do now is categorize the customers on the rows in the following three buckets:

Current Top Ten Balances
No Longer in Top Ten (if statement saying if they were top ten previous week, but are no longer)
All Other

Is there a way to create this type of categorization derived from measures? I've done a little reading on disconnected tables, but am struggling a little. Any help or reference to other posts that might help would be very much appreciated!

Thanks!