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.