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

  • 8 February 2021
  • 1 reply

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


This topic has been closed for comments

1 reply

Userlevel 6
Badge +1

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