cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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``````

3 REPLIES 3
Frequent Visitor

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

Community Support

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
Not applicable

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``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors