Forum Discussion

Sankzpower's avatar
Sankzpower
Icon for Helper I rankHelper I
6 years ago

Filling gaps with a calculated measure

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...
Need Help
Show and Tell
Tips and Tricks
AnthonyTilley's avatar
AnthonyTilley
Icon for Solution Sage rankSolution Sage
6 years ago
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's avatar
Sankzpower
Icon for Helper I rankHelper I
6 years ago

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's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    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.