Power Platform Community Forum Thread Details

Hi! 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. 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. TYIA. Any and all help is appreciated for this newbie.

Categories:

General topics

@Anonymous I was unable to find an elegant solution to your problem, but after testing this does appear to work. I tried to cut down as much as I could, but I hope it helps.

I created two dictionaries for your variables. Compose is the scoring dictionary and Count is the dictionary to use when determining total possible points available.

I then ran an Apply to Each for each row in your SharePoint List. If you are only pulling one item at a time, the apply to each would not be necessary.

Totals:

add(add(add(add(int(outputs('Compose')[item()?['Recommended']]),int(outputs('Compose')[item()?['Report']])),add(int(outputs('Compose')[item()?['Testing']]),int(outputs('Compose')[item()?['Training']]))),add(int(outputs('Compose')[item()?['KPIs']]),int(outputs('Compose')[item()?['Workflows']]))),add(if(greater(length(item()?['Link 1']),0),1,0),if(greater(length(item()?['Link 2']),0),1,0)))

Essentially you are calling the dictionary with the output of the grade selection.

outputs('Compose) = Dictionary

[item()?['Recommended']] = Your current item's value for the category. If your item's Recommended value was "Yes", this would say outputs('Compose')['Yes'], which would return a 2.

Counts:

add(add(add(add(int(outputs('Count')[item()?['Recommended']]),int(outputs('Count')[item()?['Report']])),add(int(outputs('Count')[item()?['Testing']]),int(outputs('Count')[item()?['Training']]))),add(int(outputs('Count')[item()?['KPIs']]),int(outputs('Count')[item()?['Workflows']]))),2)

Skipped the two links at the end since you said those are graded no matter what. This will provide a count of all possible grades for anything other than N/A. I used a 2 point value for each in this list due to 2 being the highest possible score possible.

Outcome:

mul(div(float(outputs('Totals')),float(outputs('Counts'))),100)

Just divides the two and multiplies by 100 to give you a percentage value. Make sure to use 'float' before each value or you will only get 100 as the result.

Output Example: