How to get an accurate aggregate average of a percentage on a line chart?

Sam1

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!

GrantSmith

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

GrantSmith

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

GrantSmith

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.

jaeW_at_Onyx

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?