SOLVED

Trying to find solution

%3CLINGO-SUB%20id%3D%22lingo-sub-1086353%22%20slang%3D%22en-US%22%3ETrying%20to%20find%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1086353%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20set%20a%20SharePoint%20list%20that%20has%20workplans%20for%20example%3A%3C%2FP%3E%3CP%3E1.1%20Workplan%20A%20(only%20has%201%20sub%20component%201.1.1)%3C%2FP%3E%3CP%3E1.2%20Workplan%20B%20(but%20workplan%20B%20has%203%20sub%20components%201.2.1%20-%201.2.2%20-%201.2.3)%3C%2FP%3E%3CP%3E1.3%20Workplan%20C%20(but%20workplan%26nbsp%3BC%20has%26nbsp%3B5%20sub%20components%26nbsp%3B1.3.1%20-%201.3.2%20-%201.3.3%20-%201.3.4%20-%201.3.5)%3C%2FP%3E%3CP%3EMy%20goal%20is%2C%20I%20can%20do%20a%20percent%20complete%20on%20each%20sub%20component%20(that%20is%20a%20manual%20entry%20by%20the%20user)%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20take%20the%20sub%20components%20and%20do%20an%20average%20of%20each%20workplan%20to%20see%20how%20much%20has%20been%20completed%26nbsp%3B(%25)%26nbsp%3Bin%20each%20of%20the%20Workplans.%3C%2FP%3E%3CP%3Efor%20example%3A%3C%2FP%3E%3CP%3Eworkplan%20B%20-%20sub%20components%201.2.1%20%3D%2050%25%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201.2.2%20%3D%2020%25%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201.2.3%20%3D%2035%25%3C%2FP%3E%3CP%3ETotal%20workplan%20B%20%25%20%3D%2035%25%26nbsp%3B%20(sub%201%20%2B%20sub%202%20%2B%20sub%203%20%3D%20105%20%2F%203%20%3D%2035%25)%3C%2FP%3E%3CP%3EI%20tried%20Calculated%20columns%20but%20they%20will%20only%20work%20on%20the%20same%20row.%3C%2FP%3E%3CP%3EAnyone%20out%20there%20that%20can%20help%20it%20would%20be%20really%20appreciated.%3C%2FP%3E%3CP%3EI%20have%20columns%20setup%20for%20WorkPlan%2C%20Subcomponent%2C%20subcomponent%20%25%2C%20and%20Workplan%20%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1086353%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1087274%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20find%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1087274%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%2C%20that%20looks%20almost%20exactly%20what%20I%20am%20looking%20for.%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20getting%20back%20to%20me.%3C%2FP%3E%3CP%3EIf%20you%20wouldn't%20mind%20posting%20the%20steps%20that%20would%20be%20great.%3C%2FP%3E%3CP%3EI%20am%20still%20fairly%20new%20at%20a%20lot%20of%20this%20but%20am%20being%20tasked%20with%20these%20type%20things%20all%20the%20time%20and%20couldn't%20find%20a%20good%20solution.%3C%2FP%3E%3CP%3EAgain%20this%20looks%20great.%3C%2FP%3E%3CP%3EIf%20the%20steps%20are%20to%20cumbersome%20for%20posting%20here%20let%20me%20know%20and%20I%20have%20no%20problem%20giving%20you%20my%20work%20email%20if%20that%20is%20allowed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%20thank%20you%3C%2FP%3E%3CP%3EClayton%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1087253%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20find%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1087253%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F504513%22%20target%3D%22_blank%22%3E%40ClaytonP%3C%2FA%3E%26nbsp%3B%20you'd%20really%20need%20to%20do%20this%20by%20building%20a%20fairly%20simple%20PowerApps%20app%20which%20you%20could%20embed%20on%20your%20SharePoint%20page%20so%20users%20wouldn't%20go%20into%20the%20list%20itself.%20They%20would%20update%20their%20individual%20component%20percentages%20and%20you%20could%20see%20the%20overall%20percentage%20complete%20as%20well%20as%20the%20percentages%20for%20the%20different%20workplans.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20444px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F163640i12FACE5DFDBC26F1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%220-SP-list.png%22%20title%3D%220-SP-list.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EBackend%20SharePoint%20list%20with%20item%20for%20each%20Workplan%20component%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20949px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F163645iDF37D075FEC9A840%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221-workplan-embedddApp.png%22%20title%3D%221-workplan-embedddApp.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EPowerApps%20app%20embedded%20in%20SharePoint%20page%20-%20users%20could%20only%20change%20the%20%25%20for%20the%20sub-component%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIf%20that's%20the%20sort%20of%20thing%20you%20want%20I'll%20post%20up%20the%20detailed%20steps.%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1099532%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20find%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1099532%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Clayton%20(%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F504513%22%20target%3D%22_blank%22%3E%40ClaytonP%3C%2FA%3E%26nbsp%3B)%20sorry%20for%20the%20delay%20with%20posting%20up%20the%20steps%2C%20I'll%20try%20to%20do%20so%20tomorrow%20morning%20(Friday).%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1148096%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20find%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1148096%22%20slang%3D%22en-US%22%3ERob%3CBR%20%2F%3EAny%20chance%20you%20can%20export%20or%20send%20me%20a%20ZIP%20file%20of%20how%20to%20do%20this.%3CBR%20%2F%3EJust%20found%20we%20have%20power%20apps%20and%20I%20am%20ready%20to%20go%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1164277%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20find%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1164277%22%20slang%3D%22en-US%22%3E%3CP%3ERob%3C%2FP%3E%3CP%3ENot%20sure%20if%20you%20got%20my%20last%20post%20or%20not.%3C%2FP%3E%3CP%3EBut%20I%20found%20our%20org.%20now%20has%20power%20aps%20and%20would%20really%20like%20to%20know%20how%20you%20did%20the%20formulas%20etc%20to%20do%20the%20roll%20up%20on%20the%20percentages.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20could%20either%20email%20me%20or%20post%20that%20would%20be%20great.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20again%20for%20all%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1164285%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20find%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1164285%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F504513%22%20target%3D%22_blank%22%3E%40ClaytonP%3C%2FA%3E%26nbsp%3B%20thanks%20for%20chasing%20me%20on%20this.%20I'll%20dig%20out%20the%20solution%20and%20post%20here%20shortly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1164297%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20find%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1164297%22%20slang%3D%22en-US%22%3E%3CP%3EMagic%2C%20and%20again%20thank%20you%20for%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1167982%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20find%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1167982%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F504513%22%20target%3D%22_blank%22%3E%40ClaytonP%3C%2FA%3E%2C%26nbsp%3B%20for%20the%20SharePoint%20list%20I%20just%20have%203%20columns%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22SP-Columns.png%22%20style%3D%22width%3A%20584px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F170618i66A52406F4A8488D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22SP-Columns.png%22%20alt%3D%22SP-Columns.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20exported%20the%20app%20which%20is%20attached%26nbsp%3Band%20hopefully%20you'll%20be%20able%20to%20import%20it%20successfully%20into%20Power%20Apps.%20You'll%20need%20to%20add%20the%20data%20source%20in%20the%20app%20as%20the%20list%20on%20your%20site.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20you%20edit%20the%20app%20you'll%20see%20the%20yellow%20triangle%20delegation%20warnings%20on%20the%20calculations.%20They%20won't%20cause%20a%20problem%20unless%20you%20have%20a%20massive%20list.%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20you%20have%20any%20problems%20or%20questions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am trying to set a SharePoint list that has workplans for example:

1.1 Workplan A (only has 1 sub component 1.1.1)

1.2 Workplan B (but workplan B has 3 sub components 1.2.1 - 1.2.2 - 1.2.3)

1.3 Workplan C (but workplan C has 5 sub components 1.3.1 - 1.3.2 - 1.3.3 - 1.3.4 - 1.3.5)

My goal is, I can do a percent complete on each sub component (that is a manual entry by the user)

I want to be able to take the sub components and do an average of each workplan to see how much has been completed (%) in each of the Workplans.

for example:

workplan B - sub components 1.2.1 = 50%

                                                 1.2.2 = 20%

                                                 1.2.3 = 35%

Total workplan B % = 35%  (sub 1 + sub 2 + sub 3 = 105 / 3 = 35%)

I tried Calculated columns but they will only work on the same row.

Anyone out there that can help it would be really appreciated.

I have columns setup for WorkPlan, Subcomponent, subcomponent %, and Workplan %

 

8 Replies
Best Response confirmed by ClaytonP (Occasional Contributor)
Solution

Hi @ClaytonP  you'd really need to do this by building a fairly simple PowerApps app which you could embed on your SharePoint page so users wouldn't go into the list itself. They would update their individual component percentages and you could see the overall percentage complete as well as the percentages for the different workplans.

 

Backend SharePoint list with item for each Workplan componentBackend SharePoint list with item for each Workplan component

 

PowerApps app embedded in SharePoint page - users could only change the % for the sub-componentPowerApps app embedded in SharePoint page - users could only change the % for the sub-component


If that's the sort of thing you want I'll post up the detailed steps.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Hi@RobElliott 

Rob, that looks almost exactly what I am looking for.

Thank you very much for getting back to me.

If you wouldn't mind posting the steps that would be great.

I am still fairly new at a lot of this but am being tasked with these type things all the time and couldn't find a good solution.

Again this looks great.

If the steps are to cumbersome for posting here let me know and I have no problem giving you my work email if that is allowed.

 

Again thank you

Clayton

Hi Clayton (@ClaytonP ) sorry for the delay with posting up the steps, I'll try to do so tomorrow morning (Friday).

Rob
Los Gallardos

Rob
Any chance you can export or send me a ZIP file of how to do this.
Just found we have power apps and I am ready to go
Thanks

Rob

Not sure if you got my last post or not.

But I found our org. now has power aps and would really like to know how you did the formulas etc to do the roll up on the percentages.

 

If you could either email me or post that would be great.

 

Thank again for all your help

@ClaytonP  thanks for chasing me on this. I'll dig out the solution and post here shortly.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Magic, and again thank you for your help

Hi @ClaytonP,  for the SharePoint list I just have 3 columns:

 

SP-Columns.png

 

I've exported the app which is attached and hopefully you'll be able to import it successfully into Power Apps. You'll need to add the data source in the app as the list on your site.

 

When you edit the app you'll see the yellow triangle delegation warnings on the calculations. They won't cause a problem unless you have a massive list.

Let me know if you have any problems or questions.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User