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

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
 10.6K All Categories
 1 APAC User Group
 12 Welcome
 36 Domo News
 9.6K Using Domo
 1.9K Dataflows
 2.4K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 339 Workbench
 251 Domo Best Practices
 11 Domo Certification
 461 Domo Developer
 47 Domo Everywhere
 100 Apps
 703 New to Domo
 84 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 22 お知らせ
 63 Kowaza
 296 仲間に相談
 649 ひらめき共有