Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LEC
Frequent Visitor

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:

CompanyTypeStateLate?WeekNum
HAWA11
CBGA 1
CAGA 1
CAGA 1
CAGA 2
CAGA 2
CAGA 4
CBGA14
CAGA14
CAGA14
CAGA 5
CAGA 5
HAWA 6
CAGA 6
5 REPLIES 5
Sean
Community Champion
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%

Cumulative %s.png

LEC
Frequent Visitor

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.

 

WeekNumCompanylate this weekDeliveries this weekcumulative on timeCumulative totalROLLING
1H11010%
1C0333100%
2H00010%
2C0255100%
3H00010%
3C0055100%
4H00010%
4C346967%
5H00010%
5C0261155%
6H011250%
6C0171258%

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

-- ------------------------------ --
Please mark solutions as complete when your question has been answered
-- ------------------------------ --
LEC
Frequent Visitor

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.

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

-- ------------------------------ --
Please mark solutions as complete when your question has been answered
-- ------------------------------ --

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors