cancel
Showing results for
Did you mean:
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.

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"

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
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.
6 REPLIES 6
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.

Helper I

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

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"

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

Super User

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.

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.
Helper I

## 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.

Helper I

Hello @v-xida-msft

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

Announcements

#### Power Apps News & Announcements

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

#### Power Apps Community Blog

Check out the latest Community Blog from the community!

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