How to calculate Sum of averages for different level of detail?

We have information in a table with Sales by Invoice ID which is mapped to a Location and Area. Like below:

Invoice ID, Invoice Date, Location, Area, Sales

We want to get a summary where we see Avg sales per Location which is filtered by dates. This is then summarized at Area level to see total Sales by Area (sum of avg Sales by location).

0 1 1,161
1 REPLY 1

Dawid
Participant V

Sounds like window functions to me. Unfortunately there’s no easy way to do it. The LookML layer allows us to avoid rewriting simple aggregations but we can’t use it in the UI to create additional Window Functions calls to group data like that.

I find myself trying to find workarounds for this at least twice a week

Top Labels in this Space
Top Solution Authors