Hi,
The issue you're encountering is due to the way you're calculating percentages for each column and then summing them up. When you sum them, if all the columns have a "true" value, the total exceeds 100% (105% in your case) because you're counting each "true" value separately and applying the percentage calculation to each of the five columns.
To calculate the correct percentage, you should first determine how many of the five columns contain "true" values for each row and then calculate the percentage based on that count relative to the total number of columns (5 in this case).
Here’s how you can adjust your formula:
- Count how many columns have "true" values for the specific row.
- Divide that count by the total number of columns (5).
- Multiply by 100 to get the percentage.
Round(
(
CountRows(
Filter(
colPercentages,
projectnumber = ThisItem.projectnumber &&
(q1cbxanswer = "true" || q2cbxanswer = "true" || q3cbxanswer = "true" || q4cbxanswer = "true" || q5cbxanswer = "true")
)
) / 5
) * 100,
0
)
However, a more straightforward approach would be to
count the number of "true" answers across all columns in a single row and then calculate the percentage based on that count. Here's a formula that should work:
Round(
(
(If(ThisItem.q1cbxanswer = "true", 1, 0) +
If(ThisItem.q2cbxanswer = "true", 1, 0) +
If(ThisItem.q3cbxanswer = "true", 1, 0) +
If(ThisItem.q4cbxanswer = "true", 1, 0) +
If(ThisItem.q5cbxanswer = "true", 1, 0)) / 5
) * 100,
0
)
Thanks
