CODEWITHSUNDEEP
powerbidaxmeasure
user13320137
user13320137

Reputation: 59

Calculate cumulative % based on sum of next row

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)

Upvotes: 1

Views: 272

Answers (2)

sam
sam

Reputation: 1304

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.

enter image description here

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

For cumulative calculation, a ordering is always required. Anyway, if your given values in column "Range" is real - this will also work for this purpose as an ascending ordering on this filed keep data in expected order. Do this following to get your desired output.

Create the following measure-

count_percentage = 

VAR total_of_count =
CALCULATE(
    SUM(your_table_name[Count]),
    ALL(your_table_name)
)

VAR cumulative_count =
CALCULATE(
    SUM(your_table_name[Count]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Range] <= MIN(your_table_name[Range])
    )
)

RETURN cumulative_count/total_of_count

Here is the final output-

enter image description here

Upvotes: 1

Related Questions