Calculating a percentage score based on dropdown list selections means finding how often a specific option is chosen relative to the total number of rows. You can use this approach for both Default and Prefilled tables. The result can be shown in a summary (prefilled) table using a formula.
Prerequisites
Before you begin make sure you have the following:
Plan: All Plan Types
Permission: Home Folder (Template Library) – Org Controller (or higher); Team Folder – Project Controller (or higher)
Skills Required: Dashpivot
Device Type: computer
Subscription: Dashpivot
Step-by-Step Instructions
Add or locate your data table (Default or Prefilled table).
Add a Prefilled table to use as your summary table.
Change the target cell in the summary table to a Formula field.
Enter the following formula to calculate the percentage of a specific dropdown selection:
=COUNTIF(Source_Table!Column:Column, "Criteria")/COUNTA(Source_Table!Column:Column)*100
Replace:
Source_Table with your data table name.
Column with the column that contains the dropdown values.
"Criteria" with the dropdown option you want to score (e.g.,
"High","Medium","Low").
Example
If your data table is named Table1 and Column E contains risk levels (High/Medium/Low), then:
To calculate % of High:
=COUNTIF(Table1!E:E, "High")/COUNTA(Table1!A:A)*100
To calculate % of Medium:
=COUNTIF(Table1!E:E, "Medium")/COUNTA(Table1!A:A)*100
To calculate % of Low:
=COUNTIF(Table1!E:E, "Low")/COUNTA(Table1!A:A)*100