How to get an accurate aggregate average of a percentage on a line chart?
I have a line chart that shows a percentage across categories (opens/delivered graphed by email campaign). I would like to show which campaigns are above or below average with a line on the graph. Because of the different sizes of the email campaigns I cannot just average the ratios  instead I need to divide all the opens by all the deliveries for filtered data. It seems that the default average in Domo won't do this, but I was able to get the overall average to show as the summary number with this beast mode:
sum(`UniqueOpens`) / sum(`NumberDelivered`)
However I can't seem to get this to show as line on the graph. The blue line is what I want to show while the black line is the average of the ratios.
I want the graphed average to change as the dataset is filtered by end users, so if they want to compare a subset of the campaigns they could see which ones are above/below average of the subset.
Thank you!
Best Answer

Hi @Sam1
Sounds like you want an average across the entire dataset to compare against. You can utilize a window function to get this and then compare your average to to it.
Overall percentage:
SUM(SUM(`UniqueOpens`)) OVER () / SUM(SUM(`NumberDelivered`)) OVER ()
Putting it all together:
CASE WHEN SUM(`UniqueOpens`)/SUM(`NumberDelivered`) > SUM(SUM(`UniqueOpens`)) OVER () / SUM(SUM(`NumberDelivered`)) OVER () THEN 'Above' WHEN SUM(`UniqueOpens`)/SUM(`NumberDelivered`) < SUM(SUM(`UniqueOpens`)) OVER () / SUM(SUM(`NumberDelivered`)) OVER () THEN 'Below' ELSE 'Same' END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers

Hi @Sam1
Sounds like you want an average across the entire dataset to compare against. You can utilize a window function to get this and then compare your average to to it.
Overall percentage:
SUM(SUM(`UniqueOpens`)) OVER () / SUM(SUM(`NumberDelivered`)) OVER ()
Putting it all together:
CASE WHEN SUM(`UniqueOpens`)/SUM(`NumberDelivered`) > SUM(SUM(`UniqueOpens`)) OVER () / SUM(SUM(`NumberDelivered`)) OVER () THEN 'Above' WHEN SUM(`UniqueOpens`)/SUM(`NumberDelivered`) < SUM(SUM(`UniqueOpens`)) OVER () / SUM(SUM(`NumberDelivered`)) OVER () THEN 'Below' ELSE 'Same' END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
One thing to note, window functions aren't available out of the box, you need to talk with your CSM to get them enabled in your instance.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
careful... unique opens cannot be aggregated over time. that's not really the definition of unique. I think i would push back to the business on this one.
If you measure Unique opens daily. and i open the email 5 times on separate days in the week. do i count once or five times?
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 602 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 691 Beast Mode
 43 App Studio
 39 Variables
 658 Automate
 170 Apps
 441 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 386 Distribute
 111 Domo Everywhere
 269 Scheduled Reports
 6 Software Integrations
 112 Manage
 109 Governance & Security
 8 Domo University
 30 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive