Cumulative percentage

[LEC]

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

[Sean]

"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%

[LEC]

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%

[Neuro81]

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

[LEC]

Ok, 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