Calculated Column Circular Error need alternative solution
Results 1 to 2 of 2

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

  1. #1
    New Member
    Join Date
    Jun 2006
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    New Member
    Join Date
    Jun 2006
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!
    Last edited by ollieotis; Jul 10th, 2019 at 06:24 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •