Calculated Column Circular Error need alternative solution

[ollieotis]

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)

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Customer[/TD]
[TD]Parent Customer[/TD]
[TD]A/R Balance[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Unit[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Parent Customer[/TD]
[TD]Current Rank[/TD]
[TD]Current Balance[/TD]
[TD]Prior Rank[/TD]
[TD]Prior Balance[/TD]
[/TR]
[TR]
[TD]Top Ten Parent Customers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Customer A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Customer B...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formerly Top Ten Parent Customers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Customer X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]













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!

 

[ollieotis]

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!

 

[Louis_Guionneau]

I haven't found a solution to this problem either. You're not alone.

 

[nikio8]

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]

@ 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?