How to calculate cumulative sum without impacting sorting order?

[eri]

Hi,  I have a table, with dimension = Scrap Code, measure= Qty(number of scrap code) I want to calculate accum coloumn & order table with Qty descending so as to get the top  scarp codes, and cumulative sum should not impact the order. Expected output- 

Scrap Code	Qty	Accum
4P	8	8
4W	7	15
4H	5	20
1I	4	24
1B	2	26

Please help

[JordyWegman · Accepted solution]

Hi Guddu,

Try this:

RangeSum(Above(TOTAL Sum(Qty), 0, RowNo(TOTAL)))

Jordy

Climber 

[eri]

I tried these but when I am using rangesum()/above() function by Qty Column is no longer sorted DESC.
I want to keep Qty column DESC always 

[JordyWegman]

That shouldn't be a problem. If you go to settings -> Sorting. Is Qty then at top? If not, put this on top.

Jordy

Climber

[eri]

Tried that already, didn't work as above funtion doesn't work with built in sorting.
Any other options please?

[JordyWegman]

I don't know why it shouldn't work, because if I use your example, it works: 

Jordy

Climber

[eri · Accepted solution]

Thanks Jordy...
Using your formula and one addition thing I discovered just now it worked for me.

So, if using range & above() function , sorting needs to be done by expression in the sorting tab under dimensions name.
This made it work 🙂

Many thanks for your help

[david_hg96]

Hi Jordy,

Thanks for your help,

it works but in my case using weeks, some of them has no data, so the cumulative gives me nulls:

how could I fix it?

Regards,

David HG