Calculate cumulative % based on sum of next row

Question

I want to calculate % based on below formula. Its little bit tricks and I am kind of stuck and not sure how to do it. Thanks if anyone can help.

I have few records in below table which are grouped by Range

Range       Count
0-10        50
10-20       12
20-30       9
30-40       0
40-50       0
50-60       1
60-70       4
70-80       45
80-90       16
90-100      7
Other       1

I want to have one more column which has the cumulative % based on sum of next row against total row count (145), something like below

Range       Count   Cumulative % of Range
0-10        50      34.5%   (which is 50/145)
10-20       12      42.7%   (which is 62/145)
20-30       9       48.9%   (which is 71/145)
30-40       0       48.9%   (which is 71/145)
40-50       0       48.9%   (which is 71/145)
50-60       1       49.6%   (which is 72/145)
60-70       4       52.4%   (which is 76/145)
70-80       45      83.4%   (which is 121/145)
80-90       16      94.5%   (which is 137/145)
90-100      7       99.3%   (which is 144/145)
Other       1      100.0%   (which is 145/145)

Answer 1

Follow the below steps to get your answer. Please vote and accept the answer, If you find the solution helpful.

1st step - - Create an index column from your range column. I have replaced "Other" Value to 999. You can replace it to much bigger number, which is unlikely to be there in your dataset. Convert this new column into whole number

Sort Column = if(Sickness[Range] = "Other",9999,CONVERT(LEFT(Sickness[Range],SEARCH("-",Sickness[Range],1,LEN(Sickness[Range])+1)-1),INTEGER))

2nd Step - Use the below measure to get value:

Measure = 
var RunningTotal = CALCULATE(SUM(Sickness[Count]),FILTER(all(Sickness),Sickness[Sort Column] <= MAX(Sickness[Sort Column])))
var totalSum = CALCULATE(SUM(Sickness[Count]),ALL())
Return
RunningTotal/totalSum

Below is the output that exactly matches your requirement.