I have a table with data that looks like this (example):Company Team Employee Name Height Weight XYZ Alpha Alex 1.91 m 88 kg XYZ Alpha Peter 1.69 m 59 kg XYZ Beta Alex 1.91 m 88 kg XYZ Beta Doug 1.80 m 72 kg
Alex is working 50% for team Alpha and 50% for team Beta.
Now i'd like to create a Drill-Down-Chart that shows me the average employee height for the company (layer 1) and each team (layer 2).
The calculations i expect to be made are:
Average height Company XYZ = (1.91 + 1.69 + 1.80) / 3
from there i can drill down to
Average height Team Alpha = (1.91 + 1.69) / 2
Average height Team Beta = (1.91 + 1.80) / 2
I tried several custom calculations but cannot get to this result. Can somebody help me?