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!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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:
Upvote 0
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
 
Upvote 0
@ 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?
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top