how to aggregate nested average case statement
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`)
Answers

sum(count(distinct case when AVG(
SignUps
) >= AVG(`Target`) thenStoreNumber
end)) fixed (byRegion
)/
sum(count(distinct
StoreNumber
)) FIXED (byRegion
)
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman1
Categories
 All Categories
 1.2K Product Ideas
 1.2K Ideas Exchange
 1.3K Connect
 1.1K Connectors
 273 Workbench
 2 Cloud Amplifier
 3 Federated
 2.7K Transform
 78 SQL DataFlows
 525 Datasets
 2.1K Magic ETL
 2.9K Visualize
 2.2K Charting
 434 Beast Mode
 22 Variables
 511 Automate
 114 Apps
 389 APIs & Domo Developer
 8 Workflows
 26 Predict
 10 Jupyter Workspaces
 16 R & Python Tiles
 332 Distribute
 77 Domo Everywhere
 255 Scheduled Reports
 66 Manage
 66 Governance & Security
 1 Product Release Questions
 Community Forums
 40 Getting Started
 26 Community Member Introductions
 68 Community Announcements
 4.8K Archive