Filling gaps with a calculated measure

[Sankzpower]

Hi guys

Hope my fellow power bi friends help me. I have a simple table sample which I could easily get it done in excel with the result what I need. However, I cannot seem to get it work in Power BI. Basically, I am filling the gaps and changing value in the value_missing column using a measure with the help of profile column. 

1st calculation = (Sum of all Value_missing column) multiply (row Profile value)

2nd Caculation = Sum(Profile column where value_missing is >0)

Final result = 1st Calculation/2nd Calculation

Sample file here in excel

Months	Profile	Value_Missing	New_Filled	1st Calculation	2nd Calculation	Final Result
Jan	10%	745	757	599	79%	757
Feb	9%	860	681	539	79%	681
Mar	8%	475	605	479	79%	605
Apr	7%		530	419	79%	530
May	6%	578	454	360	79%	454
Jun	7%	569	492	389	79%	492
Jul	7%	845	507	401	79%	507
Aug	7%	235	514	407	79%	514
Sep	8%		567	449	79%	567
Oct	7%	654	560	443	79%	560
Nov	9%	574	658	521	79%	658
Dec	10%	457	764	605	79%	764

[AnthonyTilley]

1st Calculation = 
Var Pr = Sum('Table'[Profile])
var T_VM = CALCULATE(sum('Table'[Value_Missing]),all('Table'))
Var f1 = T_VM * Pr

return f1

2nd Calculation = 
var T_VM = CALCULATE(sum('Table'[Profile]),ALL('Table'),'Table'[Value_Missing]>0)

return T_VM

Fianl Calculation = DIVIDE([1st Calculation],[2nd Calculation],0)

New_Filled = 
Var Pr = Sum('Table'[Profile])
var T_VM = CALCULATE(sum('Table'[Value_Missing]),all('Table'))
Var f1 = T_VM * Pr
var T_PR = CALCULATE(sum('Table'[Profile]),ALL('Table'),'Table'[Value_Missing]>0)
var ret = f1/T_PR
return ret

[Sankzpower]

Thanks @AnthonyTilley 

I understand the logic behind that now. However, when I tried at my end its not coming up with the same result. I think its because I have got filters on as the one which I have attached is for one ID. I have multiple Ids with Jan - Dec records. I did try the below using your code but it would not work:

1st Calculation = 
Var Pr = Calculate(Sum('Table'[Profile]),all('Table'[IDs])
var T_VM = CALCULATE(sum('Table'[Value_Missing]),all('Table'[IDs]))
Var f1 = T_VM * Pr

return f1

[Icey]

Hi @Sankzpower ,

How about replacing ALL with ALLSELECTED? Can it work?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.