Showing progress on a percentage basis from excel

[AbdulHannan]

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

[WarrenBelz]

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.

[AbdulHannan]

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"

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

[WarrenBelz]

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 · Accepted solution]

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,

[AbdulHannan]

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.

[AbdulHannan]

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