Calculated Column Circular Error need alternative solution

ollieotis

New Member
Joined
Jun 6, 2006
Messages
44
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)

CompanyCustomerParent CustomerA/R Balance

<tbody>
</tbody>




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

CustomerUnit

<tbody>
</tbody>

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 CustomerCurrent RankCurrent BalancePrior RankPrior Balance
Top Ten Parent Customers
Customer A
Customer B...
Formerly Top Ten Parent Customers
Customer X

<tbody>
</tbody>













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!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

ollieotis

New Member
Joined
Jun 6, 2006
Messages
44
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!
 
Last edited:

nikio8

Board Regular
Joined
Oct 20, 2017
Messages
128
Hi, I did something like this and it was the most difficult task that I have ever done. lots of these Filter(ALL(....

Pivot tables already have top 10.
You can create two pivot tables and say filter by week.
Previous top 10, filter out paid date - last day or last week,
Today, no filters.

You could create another "Calendar table" and use timeline to control which data you see.
Kind regards
 

Louis_Guionneau

New Member
Joined
Nov 12, 2019
Messages
28
@ nikio - nice one. did you have to create a bunch of calculated columns to do that? maybe it's possible to get around it using a combination of top n / if?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,438
Messages
5,486,891
Members
407,570
Latest member
cannotquitexcel

This Week's Hot Topics

Top