cancel
Showing results for
Did you mean:
Frequent Visitor

Rank based on cumulative total

I have operation and values sequence and want to rank them if the cumulative sum of earlier is equal or less than 30.

Data set

 id operation value 1 op1 10 2 op2 20 3 op3 30 4 op4 15 5 op5 15 6 op6 10

Expected Outcome

 id operation value cumulative value Rank 1 op1 10 10 1 2 op2 20 30 1 3 op3 30 30 2 4 op4 15 15 3 5 op5 15 30 3 6 op6 10 10 4

3 REPLIES 3
Community Support Team

Re: Rank based on cumulative total

Hi @pateam

create calculated columns

cumulative = CALCULATE(SUM(Table1[value]),FILTER(ALL(Table1),[id]<=EARLIER([id])))

int = INT(Table1[cumulative]/30)

earlier = LOOKUPVALUE(Table1[int],Table1[id],Table1[id]-1)

diff = Table1[int]-Table1[earlier]

group = IF(Table1[diff]=1,CALCULATE(SUM(Table1[diff]),FILTER(Table1,Table1[id]<=EARLIER(Table1[id]))),BLANK())

rank = IF(Table1[group]=BLANK(),CALCULATE(FIRSTNONBLANK(Table1[group],1),FILTER(Table1,Table1[id]>EARLIER(Table1[id]))),Table1[group])

If you have more values in this table, you would see all ranks.

Best Regards

Maggie

Frequent Visitor

Re: Rank based on cumulative total

Dear Maggie,

Thank you very much. struggled few days on this.

Highlighted
Frequent Visitor

Dear