cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AbdulHannan
Helper I
Helper I

Showing progress on a percentage basis from excel

Hi,
I have an app where I will receive some data on how much work is done for an item on a daily basis.
I am using excel table as data source.
What I want to do is, show progress on a percent basis as to how much work is done on an item.
I have a fixed number for each item.

The amount of work done is different every day, so to calculate the total amount of work done on an item I am using SUMIF formula on a different excel sheet which is not connected to the app.

I can't seem to figure out, how to get the total amount of work done, to calculate the percentage of work done on an item to show in app.

 

r.png

EDIT:

I have 300+ items with different ID. Every item is identified with a unique ID.
Each item will have a new row created with necessary metadata as the user reports the work progress every day.
So I need a way to sum up the work progress for each item, from the different rows, from the every day reports.
And use that value to calculate the progress against the quantity.

my column names are,
work needed to be done = Quantity
Work done so far = Work Done
The cumulative of WorkDone for each item = Total WorkDone

Now I am showing progress with this formula,
(ThisItem.'Work Done'/ThisItem.Quantity)*100 & "% Completed"

phs.PNG

Which doesn't work because I don't have the cumulative value of the total amount of work done for a item.
How can I calculate the sum of all the rows based on a items ID?

 

Any suggestion on this matter will be a great help.

 

Regards,

Abdul Hannan

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @AbdulHannan ,

Could you please share a bit more about the Item ID that you mentioned? e.g. 64-100, 4-180, ...?

Do you want to calculate the sum of the 'Work Done' column value of all related records based on Item ID?

 

Based on the needs that you mentioned, I think the Filter function and Sum function could achieve your needs. Please consider modify your formula within the Percentage Label to following:

(
  Sum(Filter('Your Excel Table', ID = ThisItem.ID), 'Work Done')/ ThisItem.Quantity
) * 100 & "% Completed"

Please consider take a try with above solution, then check if the issue is solved.

 

In addition, if you want to remain two decimals within your percentage, please modify above formula as below:

Round(
      (Sum(Filter('Your Excel Table', ID = ThisItem.ID), 'Work Done')/ ThisItem.Quantity
) * 100 , 2) & "% Completed"

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
WarrenBelz
Super User
Super User

Hi @AbdulHannan ,

Using the following names (change them to yours), WorkNeeded and WorkDone for the two fields in the Gallery and WorkGallery for the Gallery.

Sum(WorkGallery.AllItems,WorkDone)

will get the total work done

Sum(WorkGallery.AllItems,WorkDone)/Sum(WorkGallery.AllItems,WorkNeeded)*100

would get you the total percentage done.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi @WarrenBelz 

Thank you for replying.

But unfortunately I did not understand what you are trying to tell me. 😞

 

I'll add more info so you understand what I am trying to achieve,

I have 300+ items with different ID. Every item is identified with a unique ID.
Each item will have a new row created with necessary metadata as the user reports the work progress every day.
So I need a way to sum up the work progress for each item, from the different rows, from the every day reports.
And use that value to calculate the progress against the quantity.

my column names are,
work needed to be done = Quantity
Work done so far = Work Done
The cumulative of WorkDone for each item = Total WorkDone

Now I am showing progress with this formula,
(ThisItem.'Work Done'/ThisItem.Quantity)*100 & "% Completed"

phs.PNG

Which doesn't work because I don't have the cumulative value of the total amount of work done for a item.

How can I calculate the sum of all the rows based on a items ID?

 

Regards,

Abdul Hannan

Hi @AbdulHannan ,

If the table you mentioned containing all of the IDs was called SPWork (change to your name), the total of all work done would be

Sum(SPWork,'Work Done')

The total of work needed would be 

Sum(SPWork,Quantity)

The percentage of work done would be

Sum(SPWork,Quantity)/Sum(SPWork,'Work Done')*100

It is the same principle as the gallery totals I posted earlier.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

v-xida-msft
Community Support
Community Support

Hi @AbdulHannan ,

Could you please share a bit more about the Item ID that you mentioned? e.g. 64-100, 4-180, ...?

Do you want to calculate the sum of the 'Work Done' column value of all related records based on Item ID?

 

Based on the needs that you mentioned, I think the Filter function and Sum function could achieve your needs. Please consider modify your formula within the Percentage Label to following:

(
  Sum(Filter('Your Excel Table', ID = ThisItem.ID), 'Work Done')/ ThisItem.Quantity
) * 100 & "% Completed"

Please consider take a try with above solution, then check if the issue is solved.

 

In addition, if you want to remain two decimals within your percentage, please modify above formula as below:

Round(
      (Sum(Filter('Your Excel Table', ID = ThisItem.ID), 'Work Done')/ ThisItem.Quantity
) * 100 , 2) & "% Completed"

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xida-msft 

Thank you for your reply.

## Could you please share a bit more about the Item ID that you mentioned? e.g. 64-100, 4-180, ...?

>> So, I have 300+ item. To Identify the each item we are using the item ID as mentioned. e.g. 64-100, 4-180, ...

 

## Do you want to calculate the sum of the 'Work Done' column value of all related records based on Item ID?

>> Yes. 

 

I'll try the solution you provided and let you know.

 

@WarrenBelz  thank you for your reply but your solution did not work for me, or maybe I couldn't implement it the right way.

 

Regards,

Abdul Hannan.

Hello @v-xida-msft 

Your code worked perfectly.

I am using a SharePoint list as data source now.

I am getting a warning. Saying "Delegation warning. The highlighted part of this formula might not work correctly on large data sets. The "Sum" operation is not supported by this connector."

Is it because I am using a SharePoint list?

 

Thank you very much.

 

Regards,

Abdul Hannan

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,335)