Forum Discussion
Cumulative percentage
Community Champion"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% |
Neuro81Helper I
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 thoughOk, do you rececommend I create all of those extra columns as measures? I only showed them to be more descriptive in my second response. The original post is what my base level data includes.
That all being said, would it be better to do this in excel and load it in? I've always done this report in excel just using sumifs to get the rolling calculations quite simply. I was just under the impression that by doing the calculations in power bi it will be able to calculate and change as i click through other graphs, etc.
- Neuro81
In terms of creating measures, I tend to create all the individual measures that build it up to get the answer I am looking for.
and any measures that might not be useful to the audience I click and hide it from the client tools ( its called hide in report view in powerbi desktop)
You can obviously create mighty measures, but they can sometimes by a bit of an eyesore and a small headache when it comes to debugging them. Descriptions will help you and others who look at it in the future though.
At work I do everything in Excel (create calculated columns,measures and do any scrubbing in power query) and then upload to the powerbi service for others to consume.
But I guess you meant should you do all the leg work in excel tables, personally and say avoid it, get used to writing measures and just keep trying there will be others here who will want to help you