Forum Discussion
Running Total based on Rank
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
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
RankCTCF
Disclaimer: 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