Hi,
ok so I have this data:
ID Product | No. Process | Process | Hours |
1ABC | 10 | kfdsmkfsl | 1 |
1ABC | 20 | dklafklamdf | 1.5 |
1ABC | 30 | dasdlafndlf; | 2 |
1ABC | 35 | alkslafmldaf | 1 |
1ABC | 40 | nksalkdjla | 1 |
2FRW | 20 | kdlsjfadnf | 2 |
2FRW | 30 | sdnkafmlaf | 3.5 |
2FRW | 40 | djklaflada | 10 |
2FRW | 50 | sdkaflad | 2.5 |
and I have SharePoint List to record the process, but the history process will be replaced to the new one. But I want to count the hours with the processes I've passed. Because the process not always based on 'No. Process'.
For now, I used this formula:
Round((Sum(Filter(AllRecapSOW, 'ID Product' =ThisItem.'ID Product' && 'No. Process' < ThisItem.'No. Process'),Hours)) / (Sum(Filter(AllRecapSOW, 'ID Product' =ThisItem.'ID Product'),Hours)) * 100,2) & " " & "%"
Can anyone help me to fix my formula? thankyou!
I'm sorry for the confusion. There's nothing wrong with formula, but the formula just work for flow like this:
-the first process that already submitted in SP list "Tracking Process" is No. Process 20.
then when,
1. I choose ID 2FRW and No. Process 40 from database AllRekapSOW as second step
2. I submit that data to my SP list (lets call it "Tracking Process"), so the previous data (No. Process 20) will be replaced by No, Process 40.
3. then, the label in gallery which is displayed the %progress will calculate like:
(2+3.5) / total hours ID 2FRW.
Thats not what I want, because No. Process 30 has not been passed. Its should be,
(2) / total hours ID 2FRW.
So, I want to know, how to revision the formula so I get flow what I want. Can you help me? Thank you
I'm sorry for the confusion. There's nothing wrong with formula, but the formula just work for flow like this:
-the first process that already submitted in SP list "Tracking Process" is No. Process 20.
then when,
1. I choose ID 2FRW and No. Process 40 from database AllRekapSOW as second step
2. I submit that data to my SP list (lets call it "Tracking Process"), so the previous data (No. Process 20) will be replaced by No, Process 40.
3. then, the label in gallery which is displayed the %progress will calculate like:
(2+3.5) / total hours ID 2FRW.
Thats not what I want, because No. Process 30 has not been passed. Its should be,
(2) / total hours ID 2FRW.
So, I want to know, how to revision the formula so I get flow what I want. Can you help me? Thank you
I'm sorry for the confusion. There's nothing wrong with formula, but the formula just work for flow like this:
-the first process that already submitted in SP list "Tracking Process" is No. Process 20.
then when,
1. I choose ID 2FRW and No. Process 40 from database AllRekapSOW as second step
2. I submit that data to my SP list (lets call it "Tracking Process"), so the previous data (No. Process 20) will be replaced by No, Process 40.
3. then, the label in gallery which is displayed the %progress will calculate like:
(2+3.5) / total hours ID 2FRW.
Thats not what I want, because No. Process 30 has not been passed. Its should be,
(2) / total hours ID 2FRW.
So, I want to know, how to revision the formula so I get flow what I want. Can you help me? Thank you
Hi @firda59,
Do you want to get the sum of items whose 'No.Process' is less than the current item's and the sum of all items with the same 'ID Product' and get division of two values?
Do you have some error message of your expression?
For example, the current item in gallery is
The value is 1/(1+1.5+2+1+1).
I did some test according to your requirements your expression works well.
If I misunderstood your requirements, please point out the error.
Best Regards,
Kyrie
Hi @firda59,
It looks like you're using PowerApps formulas to calculate the percentage of hours for a specific process compared to the total hours for a given product. If I understand correctly, you want to calculate the percentage of hours spent on processes with a lower 'No. Process' value than the current one for the same 'ID Product'. However, you want to consider the historical processes and their hours, even if they have been replaced.
Your formula is on the right track, but it seems you're missing the part where you're actually replacing the old processes with new ones in terms of hours calculation. To achieve this, you can modify your formula as follows:
Round(
(
Sum(
Filter(
AllRecapSOW,
'ID Product' = ThisItem.'ID Product' &&
'No. Process' <= ThisItem.'No. Process' &&
'Process' = ThisItem.'Process'
),
Hours
)
) /
(
Sum(
Filter(
AllRecapSOW,
'ID Product' = ThisItem.'ID Product' &&
'Process' = ThisItem.'Process'
),
Hours
)
) * 100,
2
) & " " & "%"
In this modified formula, I've added an additional condition to the filter to consider only the processes that match the 'Process' value of the current item.
Please mark my post as Solution to help others and If you like my response please give me a Thumps Up.
Cheers!
Hi @firda59,
You mentioned that you want to fix the formula - what does the formula do now? Does it give a wrong result / give an error, etc?
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Follow me online.