Average of an Average of an Average
Hello,
I have a project that is using an interesting methodology to score social media ads/campaigns/accounts. They first score each ad, then they average the ad level scores to get the campaign score, then they average the campaign scores to get the account scores.
In the example above I score the ad based on the impressions:
case when impressions < 20 then 1
when impressions < 40 then 2
when impressions < 60 then 3
when impressions < 90 then 4 else 5 end
This gives me my ad level score. Then I need to average the ad scores to get the campaign score, and average the campaign score to get the account score.
I would also need to use the above to create cards that are rolled up at the campaign and account level:
Please help!
Answers

Hi @JustinB
You'd need to use an dataflow to preaggregate your data. You can do this in Magic ETL with the group by tile.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 
or a window function in data set views.
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 
I might recommend using a Pivot table with the Hierarchy for Campaign and Ad set on the Rows and set the Aggregation to Average on your Ad Score beastmode.
When you collapse at the Campaign level, this summary becomes more clear
Jacob Folsom
**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 
Can you show me your calculation? My pivot table will not allow me to aggregate my beast mode? My calculation is also far more complex (it creates a score using many metrics).
0 
@JustinB ,
case when impressions < 20 then 1
when impressions < 40 then 2
when impressions < 60 then 3
when impressions < 90 then 4 else 5 end
If you need to use SUM(Impressions) then it will no longer allow you to average. So that impressions number would need to be rolled up to the granularity needed for the scoring model. Otherwise, it will simply take the row level impressions.
I tried this with data stored at daily level rolled up to Ad, and it doesn't work properly.
Jacob Folsom
**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 
Hey @JustinB
Did a little tutorial for you on YouTube.
If you're in the Domodojo instance, here's a link to the Dataflow
https://domodojo.domo.com/datacenter/dataflows/53/graph
Link to DataCrew post
https://datacrew.circle.so/c/articlesandknowledgebase/tutorialbuildaverageofanaverage
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"1