cancel
Showing results 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

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

 Month HLD ID Milestone ID Planned % Milestone Update 01.04.2022 1 101 25 20 01.04.2022 1 102 10 10 01.05.2022 1 101 20 20 01.05.2022 1 102 10 10 01.06.2022 1 101 20 20 01.06.2022 1 102 10 10 01.07.2022 1 101 10 10 01.07.2022 1 102 5 10 01.08.2022 1 101 10 10 01.08.2022 1 102 5 5 01.09.2022 1 101 10 10 01.09.2022 1 102 5 5 01.04.2022 2 201 8 5 01.04.2022 2 202 8 9 01.04.2022 2 203 9 7 01.05.2022 2 201 8 10 01.05.2022 2 202 7 8 01.05.2022 2 203 8 9 01.06.2022 2 201 6 5 01.06.2022 2 202 6 9 01.06.2022 2 203 10 5 01.07.2022 2 201 8 7 01.07.2022 2 202 9 7 01.07.2022 2 203 10 9 01.08.2022 2 201 9 9 01.08.2022 2 202 8 5 01.08.2022 2 203 10 8 01.09.2022 2 201 8 9 01.09.2022 2 202 8 8 01.09.2022 2 203 8 6

 HLD ID Milestone ID % Cont 1 101 40 1 102 60 2 201 20 2 202 30 2 203 50

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
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]``

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

3 REPLIES 3
Community Support

Hi @DemoFour ,

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

Best Regards,
Henry

Responsive Resident

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.

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]``

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.