Running Total based on Rank

[kjh252]

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 to create a temporary table:

-------------

TEMP = SUMMARIZE(

ALLSELECTED(LIST),

LIST[Core Function],

"Comp_Rank",

Sum(RISK[Compliance Risk Ranking])

)

-----------------------------

Which give me:

Core Function	Comp_Total
Procure to Pay Processing	56
Capital Asset Coordination	9
Expense Report Processing & Approval	6
Other	6
PCard Coordinator	6
Policy & Regulatory Compliance	6
Monitoring Travel Advance, Unused Airfare, and Prepaid Expenses	5
Deposit Processing	4
Accounts Receivable Processing	4
Contract Review - Service Providers and IC's	3
Accounts Receivable Aging	2
Card Application Process	2
T&M Uncleared Card Notifications	2
Card Reporting	1
Training, Guidance & SME	1
Annual Card Certification	1
Concur Reconciliation	1

Then I have the below code to provide the ranking (i included a RAND() to get each row to be unique and this seems to be working). I tried various permutations and combinations of CALCULATE, SUM, using various filters: TOPN, EARLIER, MAX, but many of these it won't let me use because it is a variable and not a column.  I can't seem to get the cumulative sum to work. Below code I hard coded the "vRank <= 3" but this still adds up the whole "Comp_Total" and only shows for the first two rows (the rows with rank <=3).  

---------------------------

CUM_SUM =

VAR vRank =

IF(HASONEVALUE(TEMP[Core Function]),

CALCULATE(

RANKX(

ALLSELECTED(TEMP),

TEMP[Comp_Total],,

DESC,

Skip) + RAND()))

VAR vTotal_Comp =

CALCULATE(SUM(TEMP[Comp_Rank]),ALLSELECTED(TEMP))

-- SUMX(ALL(TEMP[Core Function]),[Comp_Total])

VAR vCurr_Comp =

SUM(TEMP[Comp_Rank])

VAR vCum_Total =

CALCULATE(

[Comp_Total],

FILTER(

ALL(TEMP),

vRank <= 3

)

)

RETURN

vCum_Total

-------------------------------------------

Which give me:

Core Function	Comp_Total	Comp_Rank	vRank	CUM_SUM
Procure to Pay Processing	56	56	1.04	115
Capital Asset Coordination	9	9	2.94	115
Expense Report Processing & Approval	6	6	3.27
Other	6	6	3.3
PCard Coordinator	6	6	3.48
Policy & Regulatory Compliance	6	6	3.49
Monitoring Travel Advance, Unused Airfare, and Prepaid Expenses	5	5	7.92
Deposit Processing	4	4	8.2
Accounts Receivable Processing	4	4	8.48
Contract Review - Service Providers and IC's	3	3	10.18
Accounts Receivable Aging	2	2	11.09
Card Application Process	2	2	11.11
T&M Uncleared Card Notifications	2	2	11.22
Card Reporting	1	1	14.39
Training, Guidance & SME	1	1	14.73
Annual Card Certification	1	1	14.84
Concur Reconciliation	1	1	14.87

Any help would be appreciated. I am frustrated with spending so much time trying to figure this out for something that seems like it should be a bit easier : (  I know I am probably struggling with column vs. measure, but have been all over the boards trying multiple approaches.

Thanks

[kjh252]

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

[v-alq-msft]

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.

[Anonymous · Accepted solution]

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