How do I get my row report to show the AVG of different parent rows rather than the children?
My source sheet has lots of parent and children rows: 13 Level 1 processes have a total of 27 Level 2 processes which in total have 60 Level 3 processes, each including at least one metric (153 in total).
So the metrics are the lowest level and roll up into their respective L3 process. The L3 processes roll up into their respective L2, and the L2 processes roll up into their respective L1 process. I can do the math in Excel and the source sheet, but due to various reasons I want to see if a report can do the same for easier reading as my source sheet has more than 500 rows.
I can add the AVG, but it seems as if the report calculates the average for the parent rows L2 (and L1) based on the metrics and not the respective L3 (and L2) children. Is there a way to let the report calculate differently based on the row I'm looking at?
Example is attached. The average of the L3 processes is calculated based on the metrics underneath and correct (100% and 50%). The average for the L2 process (calculated at 80%) should be based on the average of the two L3 processes underneath (=100+50= 150/2= 75%), but seems to calculate the children (=400/5=80%) instead. How can I solve for this, if at all with a report? And yes, I know about average of averages, but this is what we want to do here. 😬