Beast mode to only show matching creative types in grouped bar chart?

cthtcc

data set example.png

desired result.png

We are using a grouped bar chart to compare client and typical engagement rates by creative type (see 1st screen grab). The report is filtered by a SalesQuote field where client results have one value and typical results='Banner Average'. These are unique rows in the data set (see 2nd screen grab). There are six 'Banner Average' rows for every possible creative type (these represent the past 6 months of performance across all creatives) Because the creative types in client campaigns vary, the report should display client & typical results only for the creative types run. We are filtering by creative type in the card but would prefer to automate this - meaning have a responsive filtering mechanism that adjusts as creative types are added. Any thoughts on how to approach this in Beast Mode (or even ETL)?

trafalger

Would something like this work?

Case when SUM(SalesQuote) <> 0 and SUM(BannerAverage) <> 0 then 'Show' else 'Hide' end

trafalger

How are you calculating the average? Could you just go a group-by in the ETL by Creative type and then append it to the dataset? Then if anything new is added it would just get added automatically.

trafalger

Just something like this:

Screenshot 2023-08-31 at 9.40.33 AM.png

cthtcc

HI @trafalger the 'Banner Average' value in the SalesQuote field for typical performance is misleading. We're actually calculating an overall monthly engagement rate across 6 months rather than an average for typical performance of creatives

trafalger

So do the same logic, but use @GrantSmith's post here on how to calculate rolling averages:

https://community-forums.domo.com/main/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages

cthtcc

@trafalger I'm not looking so solve for calculating roling averages. We have 16 different creative types with more coming, so a join to actual performance data rows increases the storage impact we dont' want.

What we need to solve for is whether it is possible to automate filters so the card displays an engagment rate only when both the client SalesQuote value and the Banner Average field have data for the same Creative Type.

trafalger

Would something like this work?

Case when SUM(SalesQuote) <> 0 and SUM(BannerAverage) <> 0 then 'Show' else 'Hide' end