Referencing values from other Team Members

Hello Community,

I am trying to solve a problem for calculating a normalized value for the whole team compared to an individual contributor.
This means I need to reference Values from other rows and connect them.
For example, the list of workers that I’m working looks like this:
team_members = [‘X’, ‘Y’, ‘Z’, …, ‘The Whole Team’]

For each worker and the whole team it would look something like this

If I want to normalize say for Worker X, it would look something like this:

Level A: 50x15 = 750
Level B: 100x30=3000
Level C: 200x100=20000

Total: 750+3000+20000=$23750

This needs to be divided by the sum of level A to C for Worker X (15+30+100)

Final result: 23750 / (15+30+100) = $163.79

This calculation is pretty simple, and achieving this in Excel is very easy. However, in QuickSight this is very difficult. I don’t know how can I reference values from each worker and combine them with the whole team.
Because I have all of them(workers, and the whole team results) in a list together, using if-else statements, gets one of them excluded together with their values and the calculation doesn’t work.

The presentation is in single tables which makes it even more difficult.
Using bar charts would be easier, since I would divide them into two charts, one for the whole team and one for each worker. However tables is the requirement, and I have to make this work.

If someone knows how I might achieve this, It would be very helpful.

Is your dataset outlined like your image?

If not, can you describe how your dataset is configured?

Also, how are you importing the data? Can you use SQL?

1 Like

If it’s outline how it is in your image…

Can you try this?

your_calculation for worker_x = sumOver(avg({worker_x})*avg({the_whole_team}),[{level]}]) / sumOver(avg({worker_x})*100)


Yeah, I will try that.

I am using SQL to get the data, so I just wrote a query that solves this.
However, I still want that to get calculated like in the photo, because by using parameters the dashboard gets more dynamic.
Also, the outline is just like in the image, just it’s another dimension on top showing the date.

Thank you for your reply.

Hi, @Gent_Zhubi . We hope Max’s solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking his answer as a “Solution.”

1 Like