cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Cumulative Sum and Cumulative Sum Percentage

Hi,

I have a dataset which contains a case number, opended date, response target, response deviation in minutes. I added a custom column called First Response Calc for calculating the response target - response deviation in minutes.

 

kmarkvenas_0-1634053403771.png

 

I created a column named  _First Response (hours) which calculates the date difference in hours because the Date/Time Opened and First Reponse Calc field:

 

_First Response (hours) = DATEDIFF('Sheet1'[Date/Time Opened],'Sheet1'[First Response Calc], MINUTE) /60
 
I created an additional column called _Histo FRT which breaks down the First Response Hours based on time frames:
 
_Histo FRT = IF('Sheet1'[_First Response (hours)] < 0.25, "0-15 min",IF('Sheet1'[_First Response (hours)] < 0.5, "15-30 min", IF('Sheet1'[_First Response (hours)] < 1, "30-60 min", IF('Sheet1'[_First Response (hours)] < 2, "1-2 hr", IF('Sheet1'[_First Response (hours)] < 4 , "2-4 hr", IF('Sheet1'[_First Response (hours)] < 8, "4-8 hr", IF('Sheet1'[_First Response (hours)] < 12, "8-12 hr", IF('Sheet1'[_First Response (hours)] < 24, "12-24", IF('Sheet1'[_First Response (hours)] < 36, "24-36 hr", IF('Sheet1'[_First Response (hours)] < 48, "36-48 hr", IF('Sheet1'[_First Response (hours)] < 72, "2-3 days", IF('Sheet1'[_First Response (hours)] < 120, "3-5 days", IF('Sheet1'[_First Response (hours)] < 240, "5-10 days", If('Sheet1'[_First Response (hours)] < 480, "10-20 days"))))))))))))))
 
_Opened Cases is a column that calculates the distinct count of Case Numbers
 
_Opened Cases = CALCULATE(DISTINCTCOUNT('Sheet1'[CaseNumber]))
 
When I create a Line and Stacked Column chart I have the _History FRT on the X-axis and the _Open Cases on the Y-axis. This shows the number of Open Cases per History grouping as such (and works good). 
 
 kmarkvenas_2-1634054268814.png

 

What I would like to do is show a line value of cumulative open case percentages. For example 359 for 0-15 minutes would be 32% of the entire chart for the date range selected. Right now my line chart is only showing open case amounts for each time frame just as the bar chart is.

 

How do I calculate the cumulative amount and cumulative percentage?

 

Thanks,

Kevin

 
 
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

Modify the measure to the following form:

1. Create measure.

Measure =
var _1=COUNTX(FILTER(ALL('Sheet1'),'Sheet1'[_Histo FRT]<=MAX('Sheet1'[_Histo FRT])),[CaseNumber])
var _2=COUNTX(ALL('Sheet1'),'Sheet1'[_Opened Cases])
return
DIVIDE(_1,_2)

2. Result:

vyangliumsft_0-1634712745791.png

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

6 REPLIES 6
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

Modify the measure to the following form:

1. Create measure.

Measure =
var _1=COUNTX(FILTER(ALL('Sheet1'),'Sheet1'[_Histo FRT]<=MAX('Sheet1'[_Histo FRT])),[CaseNumber])
var _2=COUNTX(ALL('Sheet1'),'Sheet1'[_Opened Cases])
return
DIVIDE(_1,_2)

2. Result:

vyangliumsft_0-1634712745791.png

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

Anonymous
Not applicable

This works great! Thanks for the help.

v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

Here are the steps you can follow:

1. Create measure.

Measure =
var _1=COUNTX(FILTER(ALL('Sheet1'),'Sheet1'[_Histo FRT]=MAX('Sheet1'[_Histo FRT])),[CaseNumber])
var _2=COUNTX(ALL('Sheet1'),'Sheet1'[_Opened Cases])
return
DIVIDE(_1,_2)

2. Place [Measure] in Line values.

vyangliumsft_0-1634279347701.png

3. Result:

vyangliumsft_1-1634279347705.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Anonymous
Not applicable

Hi Liu,

 

The calculation you provided calculates the percentage of the total for each bar correctly (thank you).

 

I am looking to see a culmulative percentage though. If the first bar is 40%, then the second bar which is 33% should be 73.33% since it's a culumative percentage. 

 

The bars and percentages should show:

 

Bar 1: 40%

Bar 2: 73.33%

Bar 3: 86.66

Bar 4: 100%

 

Is this something that can be done?

 

Thanks,

Kevin

goncalogeraldes
Impactful Individual
Impactful Individual

Hello there @Anonymous ! Just as a suggestion, to simplify your coding steps and the readability the formulas you develop try using the SWITCH function instead of the multiple IFs() in the  "_Histo FRT". It may not only help you create simpler measures but also evaluate possible errors.

 

Regarding your question, you can check similar these threads with solutions provided for similar questions to yours:

 

https://community.powerbi.com/t5/Desktop/cumulative-running-total-percentage/m-p/384190

https://community.powerbi.com/t5/Desktop/Calculate-cumulative-percentage-from-a-calculated-column/m-p/428926

https://community.powerbi.com/t5/Desktop/cumulative-percentages/m-p/571128

 

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Anonymous
Not applicable

Hi Goncalo,

 

Thanks for your reply. I looked into those three forum questions before I sent the question out. They don't seem to work for what I want to do. I don't have a product with a sales dollar amount.

I have a number of cases within a time range. I am looking to calculate the cumulative percentage of cases as it refers to a particular time instance (0-15 minutes, 30-60 minutes, etc..)

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors