I’m new to Looker and trying to figure out how to create an Average Headcount measure. I’ve been able to do it in a Derived Table, but is it possible to do directly in an existing view? 

Below are two ways I’ve tried directly in my existing view, but I always get an error that aggregations of aggregations are not allowed or measures cannot be referenced in another measure. 

measure: daily_headcount {
    type: count_distinct
    sql: ${TABLE}.employee_id  ;;
  }

measure: average_headcount {
    type: average
    sql: COUNT(DISTINCT ${TABLE}.employee_id) ;;
  }

measure: average_headcount_2 {
    type: average
    sql: ${daily_headcount} ;;
  }

 Additionally, we’d like both a daily headcount average that could be achieved with the above (if it didn’t error), AND a simple average – where we would sum Starting Headcount and Ending Headcount and divide by 2. Started on the below, but am stuck on how to dynamically define start_of_period and end_of_period. We would want these to be based on the date range selected in an Explore. Report Date is the date that the report (including all active employees) was pulled.

measure: sop_headcount {
    type: count_distinct
    sql: CASE WHEN ${report_date} = (
      SELECT MIN(${report_date})
      FROM ${TABLE}
      WHERE ${report_date} BETWEEN {{ start_of_period }} AND {{ end_of_period }}
    ) THEN ${employee_id} ELSE NULL END ;;
    description: "Count of distinct employees at the start of the selected period."
  }

measure: eop_headcount {
    type: count_distinct
    sql: CASE WHEN ${report_date} = (
      SELECT MAX(${report_date})
      FROM ${TABLE}
      WHERE ${report_date} BETWEEN {{ start_of_period }} AND {{ end_of_period }}
    ) THEN ${employee_id} ELSE NULL END ;;
    description: "Count of distinct employees at the end of the selected period."
  }

Thanks in advance – any advice or links to documentation is much appreciated. 

Was able to get Average Headcount using a Derived Table, but it cannot be filtered by any other dimensions such as department, employee type, location, etc — it only ever gives the average headcount for the full company.