Forum Discussion
Solved
Running Total based on Rank
Hi, I have read many posts and have tried a number of different iterations and cannot seem to this to work. I am trying to get a running total of a column based on a rank. I have the below code ...
Is this the result you want? (Refer to the RunningTotal field in the table below)
Core Function Comp_Total vRank RunningTotal Procure to Pay Processing 56 1.03 115 Capital Asset Coordination 9 2.14 59 Policy & Regulatory Compliance 6 3.04 50 PCard Coordinator 6 3.05 44 Other 6 3.06 38 Expense Report Processing & Approval 6 3.08 32 Monitoring Travel Advance, Unused Airfare, and Prepaid Expenses 5 4.07 26 Deposit Processing 4 5.09 21 Accounts Receivable Processing 4 5.16 17 Contract Review - Service Providers and IC's 3 6.1 13 T&M Uncleared Card Notifications 2 7.02 10 Card Application Process 2 7.13 8 Accounts Receivable Aging 2 7.17 6 Training, Guidance & SME 1 8.01 4 Concur Reconciliation 1 8.11 3 Card Reporting 1 8.12 2 Annual Card Certification 1 8.15 1 In the table above named "TEMP", the first columns are from your sample data. The last two columns are calculated columns added using the following codes.
vRank = VAR RankCT = RANKX ( Temp, Temp[Comp_Total], Temp[Comp_Total], DESC, DENSE ) VAR RankCF = RANKX ( Temp, Temp[Core Function], Temp[Core Function], DESC, SKIP ) VAR RankCTCF = RankCT + DIVIDE ( RankCF, 100, 0 ) RETURN RankCTCFDisclaimer: In this vRank calculated column, I have given a ranking to your "Core Function" field to make the ranking unique. But that is by assuming that no two rows will have the same value in the "Core Function" field.
RunningTotal = VAR CurrentRank = Temp[vRank] VAR RT = SUMX ( FILTER ( Temp, Temp[vRank] >= CurrentRank ), Temp[Comp_Total] ) RETURN RT
Thanks sreenathv,
This works! I think I am have been confused about measures vs columns and this helped a lot. Now I am just tweaking the subgroups, but this was a huge help.
Kevin