SQL Window Functions within derived dbt metrics

Help
1

The problem I’m having

I’m trying to write a series of metrics in our semantic models that go about implementing SQL window functionality.

For instance, if we have a number of accounts each inside of a region, and I wanted to

  • Step #1: sum the amounts aggregated at the account level, then
  • Step #2: average these amounts at the region level

The context of why I’m trying to do this

We’re trying to write dynamic measures that behave this way in order to take full advantage of the relationships feature/mechanics of the semantic layer, while performing more advanced calculations given our business logic.

This functionality can be compared to the AVERAGEX or SUMX or similar window functions used in DAX to perform calculations for similar use cases against Power BI tabular models.

What I’ve already tried

  1. Window Functions in the expression of derived metrics
  - name: avg_amount_per_account
    type: derived
    label: "Average Amount per Account"
    type_params:
      expr: avg(total_amount) over (partition by {{ Entity('account')}})
      metrics:
        - name: total_amount

Error Message: dbt parse

Compilation Error
  Could not render avg(total_amount) over (partition by {{ Entity('account')}}): 'Entity' is undefined
  1. Defining entities in the derived metrics to supply to the window functions
  - name: avg_amount_per_account
    type: derived
    label: "Average Amount per Account"
    type_params:
      expr: avg(total_amount) over (partition by patientsk)
      metrics:
        - name: total_amount
      entities:
        - name: patientsk

Error Message: dbt parse

Parsing Error
  Invalid metrics config given in FilePath(.... - at path ['type_params']: Additional properties are not allowed ('entities' was unexpected)

These metrics are in preparation to write saved_queries in order to slice data at the region level, in this example. Is there a way to implement this type of dynamic logic without building separate queries and semantic models to perform the intermediate sum() aggregations?