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
DemoFour
Responsive Resident
Responsive Resident

Finding the overall % to a total

I have been given an excel spreadsheet that is used by a business entity to work out the % competion of a project milestone compared to the % contribution to the task to show how much is completed each month and if the project is on target for completion. 

First issue - I have been given whole numbers not % values - this cannot be changed as they want users to put into the spread sheet 20 for 20% to make collection simple. 

What the person wants is to replicate in PBI, is what they do on monthly individual sheets with many look ups etc. from a SUMIF on a cell by cell basis: 

 

Example

HLD 1 has 2 milestones:

milestone 101 is 40% towards the HLD

milestone 102 is 60% towards the HLD

 

By January

Milestone 101 is 90% complete and was predicted to be 95% complete

Milestone 102 is 50% complete and was predicted to be 45% complete

 

HLD calculation in excel = 

Actual completion % should be (90%*40%=36%)+(50%*60%=30%)=66%

Predicted completion % should be (95%*40%=38%)+(45%*60%=27%)=65%

 

So the difference between the two would be +1 ahead of target so on target

 

I have mocked some data up on a montly basisfor a few months using randbetween and added a dimension table 

 

MonthHLD ID Milestone IDPlanned %Milestone Update
01.04.202211012520
01.04.202211021010
01.05.202211012020
01.05.202211021010
01.06.202211012020
01.06.202211021010
01.07.202211011010
01.07.20221102510
01.08.202211011010
01.08.2022110255
01.09.202211011010
01.09.2022110255
01.04.2022220185
01.04.2022220289
01.04.2022220397
01.05.20222201810
01.05.2022220278
01.05.2022220389
01.06.2022220165
01.06.2022220269
01.06.20222203105
01.07.2022220187
01.07.2022220297
01.07.20222203109
01.08.2022220199
01.08.2022220285
01.08.20222203108
01.09.2022220189
01.09.2022220288
01.09.2022220386

 

HLD ID

Milestone ID% Cont
110140
110260
220120
220230
220350

 

I have then joined the tables on the Milestone ID and writen some measures to SUM the Milestone Update and Planned % 

My question is how can I replicate the above formula that works in excel on a cell by cell basis? 

 

How can I sum the progress, then take the % contribution and find the overall progress compared to the contribution to the project? 

Any help much appreciated 🙂 

1 ACCEPTED SOLUTION
DemoFour
Responsive Resident
Responsive Resident

I got there in the end with the following measures:

 

This measure gets the actual montly completion figure

 

% Test = SUMX('Milestone' , 'MS Update'[MS Update] * Milestone[% Cont] ) / 100

 

This measure gets the planned completion figure

 

% Plan Test = SUMX('Milestone' , 'MS Update'[MS Planned Update] * Milestone[% Cont] ) / 100

 

Then minus the two measures

Diff Test = [% Plan Test] - [% Test]

 

woohoooooo!.png

 

If anyone has a better solution, feel free to advise 🙂 

 

View solution in original post

3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

Hi @DemoFour ,

 

So far, I checked out the solution you provided and think it's a good idea.😊


Best Regards,
Henry

 

Morning @v-henryk-mstf

Thank you for your reply. I have never used excel to do data analysis and came straight into PBI and started learning DAX. So I find it  difficult when people are speaking Excel and showing look ups and formula on a cell by cell basis, when PBI is columnar and there needs to be an understanding of row context and filter context to find your overall solution. 

DemoFour
Responsive Resident
Responsive Resident

I got there in the end with the following measures:

 

This measure gets the actual montly completion figure

 

% Test = SUMX('Milestone' , 'MS Update'[MS Update] * Milestone[% Cont] ) / 100

 

This measure gets the planned completion figure

 

% Plan Test = SUMX('Milestone' , 'MS Update'[MS Planned Update] * Milestone[% Cont] ) / 100

 

Then minus the two measures

Diff Test = [% Plan Test] - [% Test]

 

woohoooooo!.png

 

If anyone has a better solution, feel free to advise 🙂 

 

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors