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.3K Product Ideas
 1.3K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 281 Workbench
 2 Cloud Amplifier
 4 Federated
 2.8K Transform
 82 SQL DataFlows
 541 Datasets
 2.2K Magic ETL
 3.2K Visualize
 2.3K Charting
 516 Beast Mode
 26 Variables
 555 Automate
 129 Apps
 408 APIs & Domo Developer
 18 Workflows
 27 Predict
 11 Jupyter Workspaces
 16 R & Python Tiles
 342 Distribute
 84 Domo Everywhere
 257 Scheduled Reports
 1 Software Integrations
 81 Manage
 80 Governance & Security
 2 Product Release Questions
 Community Forums
 41 Getting Started
 27 Community Member Introductions
 76 Community Announcements
 4.8K Archive