How do I get my row report to show the AVG of different parent rows rather than the children?

Franziska S

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. 😬

Thanks, Franziska

Genevieve P.

Hi @Franziska S

There currently isn't a way to customize how the Summaries are configured in a Report. What you're seeing (the top Summary basing its calculations on the original source rows, and not the secondary level of grouping) is currently the only way that top row can create Averages.

Please let the Product team know that you would like to be able to customize this by filling in this form, here!

In the meantime, it does sound like formulas would be the way to achieve your end-goals. You can do this in the main source sheet, or you could create a metric sheet with cross-sheet formulas for your calculations. Let me know if you need help with these formulas and I'd be happy to elaborate furhter.

Cheers,

Genevieve

Genevieve P.

Hi @Franziska S

There currently isn't a way to customize how the Summaries are configured in a Report. What you're seeing (the top Summary basing its calculations on the original source rows, and not the secondary level of grouping) is currently the only way that top row can create Averages.

Please let the Product team know that you would like to be able to customize this by filling in this form, here!

In the meantime, it does sound like formulas would be the way to achieve your end-goals. You can do this in the main source sheet, or you could create a metric sheet with cross-sheet formulas for your calculations. Let me know if you need help with these formulas and I'd be happy to elaborate furhter.

Cheers,

Genevieve

Franziska S

Thank you very much, Genevieve!