How to summarize and visualize sheet summary data of multiple sheets?



I've been using SmartSheet to track progress of different business units on a particular project. Each unit has its own sheet containing grid data that they fill out, as well as sheet summary data where I calculate different KPIs (e.g. number of records with specific status). For each unit, the sheet summary data is then summarized in a report which I use as a source for a unit-specific dashboard.

What I would like to do now is to aggregate the same information on a global level (for all units). I created a report that pulls in the sheet summary data from all the unit sheets. In the report, I added summaries to give me a global sum for each KPI. However, I'm not able to use it as a dashboard source - the Total row will not stay collapsed and the dashboard will always show the data on unit level instead of a consolidated one (please see below).

Is there any way to summarize the sheet summary data of multiple sheets and display only the Total in the report?

If not, is there a better way you could suggest to achieve a consolidate view?

Thanks in advance for your response.