cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pateam Frequent Visitor
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 

idoperationvalue
1op1 10
2op220
3op330
4op415
5op515
6op610


Expected Outcome

idoperationvaluecumulative valueRank
1op1 10101
2op220301
3op330302
4op415153
5op515303
6op610104

 

Thank you in advance.

3 REPLIES 3
Community Support Team
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])

4.png

 

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

 

 

Best Regards

Maggie

pateam Frequent Visitor
Frequent Visitor

Re: Rank based on cumulative total

Dear Maggie,

 

Thank you very much. struggled few days on this.

Highlighted
pateam Frequent Visitor
Frequent Visitor

Re: Rank based on cumulative total

Dear