0

I have a sharepoint list I am trying to figure out how to utilize power automate and flows to calculate a percentage score column that runs an initial calculation and then updates as new selections are updated or added. Each of the answers are text but are weighted differently based upon the answer.

The answers consist of Yes (2/2), Partial (1/2), No (0/2), and N/A (0/0) and belong in choice columns. These answers will update as projects are completed, so a Partial could flip to a yes. I also have two hyperlink columns that should score 1/1 with "link" present and 0/1 with no link. I have created a separate list with values but unsure what value to assign N/A since it shouldn't be counted against the total percentage and where to even begin this flow.

I've considered also adding Numerator and Denominator columns to make the percentage column easier but am unsure how to get those fields to populate since the choices are text and not numeric.

TYIA. Any and all help is appreciated by this newbie.

Sharepoint List

Scoring Sharepoint

enter image description here

Improve this question
3
  • I'm a little confused what you're trying to accomplish. Are you trying to calculate a score for each individual list item based on it's columns or are you trying to calculate an overall score based on all the list items? If you could give an example of the value you would expect to see for a few of the list items (or the whole list if that's the intent) that would really help to make sense of what 2/2 or 1/2 should correspond to.
    – theChrisKent
    Commented Mar 25, 2022 at 15:33
  • 1
    @theChrisKent Just to explain a little more each list item represents a product and the columns are a collateral inventory for each product. So does this product have testing, yes, partial, no, or N/A. The fractions are more of a points achieved/points possible. That was the best way for me to explain the N/A and it not contributing to the overall points possible and the link only counting as 1 point possible. I attached a new screenshot above that includes our percentages we calculated manually.
    – cme28460
    Commented Mar 25, 2022 at 18:33
  • Thanks for the clarification! I've put together an answer below. You can apply column formatting by using the column menu and choosing Column Settings -> Format this column -> Advanced mode, then paste the format and click Save.
    – theChrisKent
    Commented Mar 25, 2022 at 21:26

1 Answer 1

Reset to default
1

You can solve this using column formatting using the following format:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=ceiling(((if([$Link1],1,0)+if([$Link2],1,0)+if([$BuildRecommended]=='Yes',2,if([$BuildRecommended]=='Partial',1,0))+if([$Report]=='Yes',2,if([$Report]=='Partial',1,0))+if([$Testing]=='Yes',2,if([$Testing]=='Partial',1,0))+if([$Training]=='Yes',2,if([$Training]=='Partial',1,0))+if([$KPIs]=='Yes',2,if([$KPIs]=='Partial',1,0))+if([$Workflows]=='Yes',2,if([$Workflows]=='Partial',1,0)))/(2+if([$BuildRecommended]=='N/A',0,2)+if([$Report]=='N/A',0,2)+if([$Testing]=='N/A',0,2)+if([$Training]=='N/A',0,2)+if([$KPIs]=='N/A',0,2)+if([$Workflows]=='N/A',0,2)))*100) + '%'"
}

In the format above, the columns are referenced by their internal name (which might be different than yours so you'll need to update the format). Here is what it looks like (applied to the Percentage column):

enter image description here

Keep in mind that for this format to work all the columns will have to be part of the view/form.

This solution does not require lookups to another list. That information is contained in the formula above. Yay!

This cannot be solved with a calculated column because Hyperlink columns can't be used in their formulas. Everything else would be possible though.

Improve this answer
2
  • 1
    Thank you so much! Got it all figured out! Really appreciate your input!
    – cme28460
    Commented Mar 28, 2022 at 22:29
  • @cme28460 don't forget to mark the answer as accepted in case it helped you!
    – Mohamed El-Qassas MVP ♦
    Commented Mar 29, 2022 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.