Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
Proud to be a Super User!
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |