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
Hi, kjh252
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a calculated column and a measure as below.
Calculated column:
Value =
var _total=[Comp_Total]
var r1 =
RANKX(
FILTER(
ALL('Table'),
'Table'[Comp_Total]=_total
),
[Core Function]
)
var r2 =
RANKX(
ALL('Table'),
'Table'[Comp_Total]
)
return
r1+r2*10
Measure:
Rank =
RANKX(
ALL('Table'),
CALCULATE(SUM('Table'[Value])),,ASC
)
Cumulative total =
var _rank = [Rank]
return
CALCULATE(
SUM('Table'[Comp_Total]),
FILTER(
ALL('Table'),
[Rank]>=_rank
)
)
Running total 3 days =
var _rank = [Rank]
return
CALCULATE(
SUM('Table'[Comp_Total]),
FILTER(
ALL('Table'),
[Rank]>=_rank-2&&
[Rank]<=_rank
)
)
Result(including runningtal 3 days and cumulative total):
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.