Home Forums Power Pivot Dynamic Subtotals

This topic contains 0 replies, has 1 voice, and was last updated by  ollieotis 1 month, 1 week ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #18038

    ollieotis
    Participant
    • Started: 1
    • Replies: 0
    • Total: 1

    Hello,

    I’m hoping someone might be able to help point me in the right direction for a solution to the below problem.  I solved for it already using what I thought was an elegant solution, until I refreshed my data and started to learn more about circular dependencies when using calculate in calculated columns.  I have spent way more time than I care to admit trying to come up with an alternative solution, and am still stumped.  My guess is I’m just fundamentally going about it all wrong.  Any suggestions for how to approach this would be welcome.  Thanks in advance!

    My end result Pivot Table is intended to show customer balances between two points in time, subtotaled by customer balance ranking for those two points in time:

    Customer Current Rank Current Balance Prior Rank Prior Balance
    Categories…
    Top Ten Current
    Formerly Top Ten (Prior Rank)
    All Other

    I initially built this pivot table using two separate AR Balance data tables and power query to create a master fact table.  In my master fact table I created calculated columns using a calculate sum function to get the total customer balances, rankings for each customer, and then a simple if function to categorize in the final calculated column:

    Parent_C Net Overdue = calculate(sum(Aging_Current[C_PD USD]),ALLEXCEPT(Master_CoCP,Master_CoCP[Parent Customer],Master_CoCP[BU]))

    Parent_C Rank = rankx(CALCULATETABLE(Master_CoCP,allexcept(Master_CoCP,Master_CoCP[BU])),[Parent_C Net Overdue],,,DENSE)

    Duplicate above for Parent_P (Prior and Current values for parent customer), then:

    Category = if([Parent_C Rank]<=10,”Top Ten”,if(AND([Parent_C Rank]>10,[Parent_P Rank]<=10),”Formerly Top Ten”,”All Other”))

    This of course worked great until I refreshed my data, since which point I’ve been beating my head against my desk trying to make this solution work somehow.  I know I have a lot to learn with power BI still, and so would appreciate any pointers or suggestions as to how I can create my desired pivot table.

    Again, thanks!

     

     

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.