LookML: Aggregation based on Dimensions

Hi everyone,
I’m currently trying to build a model with different granularities and I have several questions regarding LookML granularity.

In general: Is it possible to generate aggregated measures in one view based on one or more dimensions? (without using derived table method)

Data:
So the data is at most detailed granularity. Table contains data showing reservation records of rooms with key info. Example showed below:

What need to be achieved:
Utilization: percentage of (used_min/daily_usable_min) or (used_min/monthly_usable_min)

Because data is at reservation level, so the utilization per room per day is the sum of (used_min/daily_usable_min) group by date and room_id. Can I do this in LookML without derived table?

In the end, I want to create things like:

  1. heat map of daily or monthly utilization.
    For daily, it would be hour_of_day and day_of_week, the utilization is the average of utilization of all rooms at given hour of day and day of week.

  2. daily or monthly utilization trends.

Also be able to filter by location or room or date.

Thanks for your inputs!

Hi Ashton,
Were you able to find a solution?

I have a similar scenario, but i’m not even able to get it to work in a derived table.

The problem I’m having goes one step further, if a user selects a particular dimension, then I need to find the subtotal for that dimension, otherwise, use grand total. To use your example, if there was another dimension, electronic key used or physical key used to enter, then the minutes metrics would be further broken down to subtotals depending on which type of key used, but if that dimension is not selected, then minutes metrics would show grand total.

Cheers,
Barry