Finding the overall % to a total

[DemoFour]

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 🙂 

[v-henryk-mstf]

Hi @DemoFour ,

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

Best Regards,
Henry

[DemoFour]

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 · Accepted solution]

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 🙂