Skip to main content

Notifications

Power Apps - Building Power Apps
Unanswered

Calculated by History Process

(1) ShareShare
ReportReport
Posted on by 372

Hi, 

 

ok so I have this data:

ID ProductNo. ProcessProcessHours
1ABC10kfdsmkfsl1
1ABC20dklafklamdf1.5
1ABC30dasdlafndlf;2
1ABC35alkslafmldaf1
1ABC40nksalkdjla1
2FRW20kdlsjfadnf2
2FRW30sdnkafmlaf3.5
2FRW40djklaflada10
2FRW50sdkaflad2.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!

  • firda59 Profile Picture
    firda59 372 on at
    Re: Calculated by History Process

    @gcmfaizan 

     

    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

  • firda59 Profile Picture
    firda59 372 on at
    Re: Calculated by History Process

    Hi @v-mingkz-msft 

     

    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

  • firda59 Profile Picture
    firda59 372 on at
    Re: Calculated by History Process

    @KeithAtherton 

     

    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

  • Re: Calculated by History Process

    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

    vmingkzmsft_0-1691743750254.png

    The value is 1/(1+1.5+2+1+1).

    I did some test according to your requirements your expression works well.

    vmingkzmsft_1-1691744059572.png

     

    If I misunderstood your requirements, please point out the error.

     

     

    Best Regards,

    Kyrie

     

  • gcmfaizan Profile Picture
    gcmfaizan 1,022 on at
    Re: Calculated by History Process

    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!

     

  • KeithAtherton Profile Picture
    KeithAtherton 3,640 on at
    Re: Calculated by History Process

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,495

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,822

Leaderboard