Skip to main content
cancel
Showing results for 
Search instead 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

Reply
kjh252
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 FunctionComp_Total
Procure to Pay Processing56
Capital Asset Coordination9
Expense Report Processing & Approval6
Other6
PCard Coordinator6
Policy & Regulatory Compliance6
Monitoring Travel Advance, Unused Airfare, and Prepaid Expenses5
Deposit Processing4
Accounts Receivable Processing4
Contract Review - Service Providers and IC's3
Accounts Receivable Aging2
Card Application Process2
T&M Uncleared Card Notifications2
Card Reporting1
Training, Guidance & SME1
Annual Card Certification1
Concur Reconciliation1

 

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 FunctionComp_TotalComp_RankvRankCUM_SUM
Procure to Pay Processing56561.04115
Capital Asset Coordination992.94115
Expense Report Processing & Approval663.27 
Other663.3 
PCard Coordinator663.48 
Policy & Regulatory Compliance663.49 
Monitoring Travel Advance, Unused Airfare, and Prepaid Expenses557.92 
Deposit Processing448.2 
Accounts Receivable Processing448.48 
Contract Review - Service Providers and IC's3310.18 
Accounts Receivable Aging2211.09 
Card Application Process2211.11 
T&M Uncleared Card Notifications2211.22 
Card Reporting1114.39 
Training, Guidance & SME1114.73 
Annual Card Certification1114.84 
Concur Reconciliation1114.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 FunctionComp_TotalvRankRunningTotal
Procure to Pay Processing561.03115
Capital Asset Coordination92.1459
Policy & Regulatory Compliance63.0450
PCard Coordinator63.0544
Other63.0638
Expense Report Processing & Approval63.0832
Monitoring Travel Advance, Unused Airfare, and Prepaid Expenses54.0726
Deposit Processing45.0921
Accounts Receivable Processing45.1617
Contract Review - Service Providers and IC's36.113
T&M Uncleared Card Notifications27.0210
Card Application Process27.138
Accounts Receivable Aging27.176
Training, Guidance & SME18.014
Concur Reconciliation18.113
Card Reporting18.122
Annual Card Certification18.151

 

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

 

 

 

 

View solution in original post

3 REPLIES 3
kjh252
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

v-alq-msft
Community Support
Community Support

Hi, @kjh252 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

 

Table:

h1.png

 

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):

h2.png

 

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 FunctionComp_TotalvRankRunningTotal
Procure to Pay Processing561.03115
Capital Asset Coordination92.1459
Policy & Regulatory Compliance63.0450
PCard Coordinator63.0544
Other63.0638
Expense Report Processing & Approval63.0832
Monitoring Travel Advance, Unused Airfare, and Prepaid Expenses54.0726
Deposit Processing45.0921
Accounts Receivable Processing45.1617
Contract Review - Service Providers and IC's36.113
T&M Uncleared Card Notifications27.0210
Card Application Process27.138
Accounts Receivable Aging27.176
Training, Guidance & SME18.014
Concur Reconciliation18.113
Card Reporting18.122
Annual Card Certification18.151

 

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

 

 

 

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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