Forum Discussion
Cumulative formula inside IF formula
Super Useryou may not need to do this cummulative.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
lbendlin ,
Thanks for your reply.
I have attached a table with what I am looking for. Planner Repair Column is what I am looking to calculate
| ToolCode | WeekName | String per week | AV | UR | RTG | Planned Repair |
| aaaa | 1 | 40 | 41 | 3 | 38 | 2 |
| aaaa | 2 | 38 | 41 | 3 | 38 | 0 |
| aaaa | 3 | 42 | 41 | 3 | 38 | 1 |
| aaaa | 4 | 44 | 41 | 3 | 38 | 0 |
| bbbb | 1 | 15 | 20 | 6 | 14 | 1 |
| bbbb | 2 | 16 | 20 | 6 | 14 | 1 |
| bbbb | 3 | 17 | 20 | 6 | 14 | 1 |
| bbbb | 4 | 19 | 20 | 6 | 14 | 2 |
Hi,
anybody can help me here?
I am lost and it is difficult also to explain in formula what I am trying to achieve.
At the moment I did in excel by adding different column everytime as per example below where:
At the end RTG should be =< String number
ToolCode String W1 String W2 String W3 String W4 Total Available (UR+RTG) UR_1 RTG_1 Planned Repair_1 UR_2 RTG_2 Planned Repair_2 UR_3 RTG_3 Planned Repair_3 UR_4 RTG_4 Planned Repair_4 aaa 40 38 42 44 41 3 38 2 1 40 0 1 40 1 0 41 0 Where
UR_1 = UR - Planned Repair_1
UR_2 = UR_1 - Planned Repair_2 etc etc
In other words I balance the UR to repair the one that I need to match WeekName
Now since I have all project in PowerBI and I have to plan for 12 weeks I was trying to put in my Dashboard in a column so that it will be easier to monitor and the dashboard should look like this. Total Available, UR and RTG will not change. Planned Repair will be recalculated considering the new count of UR - Previous Planned Repair
ToolCode WeekName String per week Total Available UR RTG Planned Repair New UR New RTG aaaa 1 40 41 3 38 2 3 38 aaaa 2 38 41 3 38 0 1 40 aaaa 3 42 41 3 38 1 1 40 aaaa 4 44 41 3 38 0 0 41 I hope this will make more sense. I have no idea how to achieve this. I was trying with cumulative, Measure and previous Raw data but still no success
Thanks a lot for your help
- lbendlin
are you trying to compare string versus AV (assuming that means availability?) ? Can you provide more context what kind of process this is?
Hi,
What I am trying to do is to calcualte the Planner Repair based on StringW and UR (Under Repair)
StringW = Demand
AV = Total Capability
UR = How many I have Under Repair
RTG = Ready to Go : AV-UR
StringWx = Is my input which is the weekly demand
AV = Is a fix number and it is my current total capability
UR = Is the starting number and based on Planned Repair this number will change over week
RTG = Is the starting number and based on Planned Repair this number will change over week.
ToolCode
String W1
String W2
String W3
String W4
Total Available (UR+RTG)
UR_1
RTG_1
Planned Repair_1
UR_2
RTG_2
Planned Repair_2
UR_3
RTG_3
Planned Repair_3
UR_4
RTG_4
Planned Repair_4
aaa
40
38
42
44
41
3
38
2
1
40
0
1
40
1
0
41
0
The formula that I am using in excel to calculate the Planned Repair_1 is:=IF(UR_1=0,0,IF(RTG_1>StringW1,0,IF(StringW1>=(UR_1+RTG_1),UR_1,MIN(UR_1,ABS(RTG_1-StringW1)))))
For the following week I have recalculated the UR_2 = UR_1-Planner Repair_1 and RTG_2 = AV-UR_1.
Therefore the new Planned Repair_2 will use the same formual but it will consider UR_2 and RTG_2
Planner Repair_2 = =IF(UR_2=0,0,IF(RTG_2>StringW2,0,IF(StringW2>=(UR_2+RTG_2),UR_1,MIN(UR_2,ABS(RTG_2-StringW2)))))
And like this for all weeks in different column (which is difficult to see)
I hope it is more clear and apologize for the confusion. I am not good in excel so that is the only way I found
I would like to have same things in 1 column in Power BI since my project is automated in PowerBI so if I can figure out the correct way it will by automatically refreshed giving me everytime the new plan and it is easier to monitor
ToolCode WeekName String per week Total Available UR RTG Planned Repair (Calculated Column) New UR(Calculated Column) New RTG(Calculated Column) aaaa 1 40 41 3 38 2 3 38 aaaa 2 38 41 3 38 0 1 40 aaaa 3 42 41 3 38 1 1 40 aaaa 4 44 41 3 38 0 0 41 Really appreciate all your support
Thanks again