Forum Discussion
Cumulative percentage
Hi,
I am having issues with rolling percentages; I have done this in excel, but need to do it in Power BI. I have read about the requirment to use FILTER in the DAX formula but I can not get it to work. I am hoping someone can help me!!
I need a rolling percentage of "on time" for deliveries to different states. The data below is much simplified, but it is possible that any number of companies could deliver to any number of states. The ones in the Late? column is a flag to indicate that delivery was not on time. What I am trying to do is create a measure that would give me these values and then I can graph it over time (with weeknum being the x axis). For example, Company H would show 0% on time for weeks 1-5, but then week 6 it would go up to 50% on time and then continue there until another delivery. Company C would be 100% for the first 3 weeks, but would drop to 66% week 4, it would climb back up to 73% the next week and so forth.
It woud be great if it was possible to roll up to all data for a certain state or all data for a certain company. Even better if i can see the information with two lines indicating "type" if I so desire.
Thank you so much!
The data is below:
| Company | Type | State | Late? | WeekNum |
| H | A | WA | 1 | 1 |
| C | B | GA | 1 | |
| C | A | GA | 1 | |
| C | A | GA | 1 | |
| C | A | GA | 2 | |
| C | A | GA | 2 | |
| C | A | GA | 4 | |
| C | B | GA | 1 | 4 |
| C | A | GA | 1 | 4 |
| C | A | GA | 1 | 4 |
| C | A | GA | 5 | |
| C | A | GA | 5 | |
| H | A | WA | 6 | |
| C | A | GA | 6 |
5 Replies
"until another delivery" would imply the % is reset when there's another delivery - so this is not really cumulative %
So you can just divide Late deliveries by total and get your late%
Right, basically "cumulative late" divided "cumulative delivery count", so it isn't being reset but just changing each week. Not like in that graph where just the percent on time that week where it will go up and down, but a smooth curve over time to show the changes. So the end of the year, a late delivery would have very little impact.
Sorry if my original post was confusing. Does this make more sense? The column "rolling" is what I am trying to do in a meaure.
WeekNum Company late this week Deliveries this week cumulative on time Cumulative total ROLLING 1 H 1 1 0 1 0% 1 C 0 3 3 3 100% 2 H 0 0 0 1 0% 2 C 0 2 5 5 100% 3 H 0 0 0 1 0% 3 C 0 0 5 5 100% 4 H 0 0 0 1 0% 4 C 3 4 6 9 67% 5 H 0 0 0 1 0% 5 C 0 2 6 11 55% 6 H 0 1 1 2 50% 6 C 0 1 7 12 58% by the sounds of it this is what you are after
_Alllates = CALCULATE(SUM(tab2[late this week]),FILTER(all(tab2[WeekNum]),tab2[WeekNum]<=Max(tab2[WeekNum])))
_AllDeliveries = CALCULATE(SUM(tab2[Deliveries this week]),FILTER(all(tab2[WeekNum]),tab2[WeekNum]<=Max(tab2[WeekNum])))
_roll = 1-DIVIDE([_Alllates],[_AllDeliveries],0)
you probably need to change the names to be a bit more useful though
