How to add a benchmarking feature to be used by a portfolio of companies?

Ask for Help
1

I’m creating a benchmarking tool for a portfolio of companies through which they will answer various questions and receive scores on their performance. In order to help each company get a sense of their performance, I want to add a Big Number component showing the average score of all respondent companies (users) in each KPI but not the individual response of each company.
I tried to use Rollup column to calculate the average in the score column, but the problem is that the score column is user-specific. For each user, the responses from other users turn to zero and lead to a miscalculation of the average score.

I hope I was clear enough. I would appreciate it if anyone shared an experience or a solution.
Thanks.

2

So the users don’t “submit” their answers, but are doing it on their own with some sort of a “custom form”?

If you can have a flow for them to submit their answers to another table, you can use the rollup column on basic column values that they have submitted.

3

Thanks for your answer.
The users are actually submitting their answers to a table. Using a Rollup column, I can get the average of each user’s responses while I want to show each user the average scores from all respondents.
As you see in the screenshots, the responses from users other than the signed-in users turn to zero in the respective user-specific column, causing a miscalculation of the average. I want a consistent average of all scores of all users of the app to be shown to every single user as a point of reference.

user2
user2117×504 4.14 KB

user1
user1115×514 4.79 KB

4

You cannot roll up User Specific column values across multiple users. There is no workaround for this.

To get an average for all users, you will need to record their scores in non-user specific columns.

Question: why are you using User Specific columns in the first place? Normally the only time to do this is when you want multiple users to store different values in the same column in the same row.

2 Likes
5

Based on your response, it seems I’ve misunderstood the application of user-specific columns. :sweat_smile:
Thanks for the clarification.

6

Yes, possibly.

If your goal is to prevent users from seeing each others responses, then there are two options:

  • Filtering: not 100% secure, but good enough if the data doesn’t contain state secrets
  • Row Owners: fully secure, but has some side effects you would want to familiarise yourself with.
2 Likes
7

You could use a user-specific columns for a form table. Then once the form is completed the answers are saved to another results table you can then do your roll-ups with.

8

The issue is that the form completion in my app is not a one-time task. Users may sign in multiple times and submit the form to check their score. I need all respondents’ average scores to be updated in real-time.

9

After each submission you can then take all the submissions by that user and get your averages.

1 Like
10
  • Use a native form to submit to the destination table. Make sure you log something like the user’s ID or email.
  • Calculate the score in the destination table.
  • Use a relation to link the users table and the destination table. Make it a multiple match.
  • Use a rollup column to calculate the average of scores for each user.
1 Like
11

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.