how to aggregate nested average case statement

brave14

Business task: "what percent of this region's sites beat their target signups YTD"? So I want to take the average daily signup for each site within a region and see if it passes its target. If it does pass, it gets a 1, else 0. After this, I then want to aggregate it to the region level. So for example if 5 out of 6 sites passed, then the passing rate would be 83% for the region.

This is my beast mode so far:

(Case 

  when AVG(`SignUps`) >= AVG(`Target`)

  then 1 

  else 0 

End)

The problem is that while this aggregation does work when I have the site column, it doesn't aggregate when I replace the site column with the region column. Assuming this problem is fixed, I then need to take the sum of all the 1's within a region and divide it by the number of stores so I can get a passing rate for a region. So I thought of how to approach this by using the beast mode below but I still have that first issue in the first part so I was wondering on how I should go about this. Thanks!

SUM(DISTINCT Case 

  when AVG(`SignUps`) >= AVG(`Target`)

  then 1 

  else 0 

End)

/

COUNT(DISTINCT `StoreNumber`)

ST_Superman

sum(count(distinct case when AVG( SignUps ) >= AVG(`Target`) then StoreNumber end)) fixed (by Region )

/

sum(count(distinct StoreNumber )) FIXED (by Region )