How to display data at all parent/child levels

[Gustavo_Eduardo]

How about Appsheet community? I want to consult something that can be very useful for many topics.

When we create a view in UX, let's say a table view, we can see the columns of the table and its rows.

When we organize it so that it is displayed in a cascade by selecting "group by", the result is very useful since it can be subselected until we obtain what we need.

If we reach the end of the selection, it is possible to see all the rest of the columns associated with the last record of the chain.

I would like to know if this is possible to do at all levels of the journey.

For example:

In the first option in the next image I have the google sheet table

The second scheme would be a group unfolded by in Ux

The third scheme is what I want to do, not only show population, area and density for the city, but as it unfolds, you can see population, area and density for the state and for the country, at the end of the day it is a SUM(SELECT())

what I want to know is if I have to create a new table where I calculate the density, surface and population data for each level or is it possible to do it without making those tables and how do I make this information appear as I unfold the levels.
If there is any test app made maybe it can help me. 

SALUDOS!

[graham_howe · Accepted solution]

Just create virtual columns in the state and country tables to sum up the metrics from the levels below, for example in the state table create a virtual column called state_population with the following formula:

SUM(
  SELECT(
    city[population], [state_id] = [_THISROW].[state_id]
  )
)

then for the country table create a VC for country_population with the following formula:

SUM(
  SELECT(
    state[state_population], [country_id] = [_THISROW].[country_id]
  )
)

You can then display these calculated columns and any other similar ones, in the table views.

[Joseph_Seddik · Accepted solution]

Hola Gus,

Yo haría los siguiente:

1. Agregar las columnas "Superficie", "Población" y "Densidad" a las dos tablas Provincias y Países.
2. Agregar una columna Ref en Provincias apuntando a Países y otra en Ciudades a Provincias.

Así, puedes por ejemplo empezar en la vista Países y tendrás dentro de cada País una lista de Provincias relacionadas, con las sumas relevantes. Si entras en una Provincia, tendrás la lista de Ciudades. Y por supuesto puedes hacer marcha atrás desde una Ciudad a su Provincia luego a su País. En cada etapa tendrás las sumas en la vista para las tres columnas correspondientes. 

Fuera de la app, ten en cuenta esta nota por favor:

Ten en cuenta que la formula para "Densidad" no será SUM([densidades relacionadas]), pero SUM([poblaciones relacionadas])/SUM([superficies relacionadas]). Matemáticamente este es correcto, pero de punto de vista estadística este formula no tendrá mucho sentido o significado. Lo más pertinente estadísticamente sería: AVERAGE([densidades relacionadas]).