Aggregation Help

[Anonymous]

Problem to solve.  Ad Groups rows are like "All Star Podiatrist Near Me" and "Podiatrist St. Louis" are showing in both tables.  This is due to to formula currently looking at each row of data, where I prefer to aggregate the all the rows for each Ad Group and assign to only one table.  Here's the current formula for CTR.  How do I update to aggregate for the total CTR for each Ad Group rather than each individual row of data?  As a bonus, how do I make the "CTR=(<"0.96")" into the Aggregate Avg CTR so automatically updates with data refresh.  Bonus items is lower priority.

=sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Clicks])

/sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Impressions])

[dwforest]

remove the 0 from the if, don't provide the false and it will return null.

[Anonymous]

David - Thanks for the quick reply.  That changes the 0 to a "--" and the rows still there.  How do I approach so that those rows do show in the visual?   Thanks,

[dwforest]

drat. so, need to put the "if" into the set expression Something like:

variable vImpressions =

If (Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=10000,

Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group]),0)

variable vTopImpressions =

Sum({$<[Ad group-Sheet1-2.Ad group]={"=$(vImpressions)>=10000"}>} $(vImpressions))

might need

Sum({$<[Ad group-Sheet1-2.Ad group]={"=$(=$(vImpressions))>=10000"}>} $(vImpressions))

[sunny_talwar]

unchecking 'Include Zero Value' under Add-ons -> Data Handling should remove 0s

[Anonymous]

Thank you all very much.  You helped me reach the end game.  Here is example of formula and final output where only showed those Ad Groups with Aggregate CTR > 5% and Conv Rate > 9.6%.   Much appreciate the coaching.

=if(Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Clicks]),[Ad group-Sheet1-2.Ad group])

/Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=.05,

if(Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Conversions]),[Ad group-Sheet1-2.Ad group])

/Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Clicks]),[Ad group-Sheet1-2.Ad group])>=.096,

Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])/

Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Total Impressions]),[Ad group-Sheet1-2.Ad group]),0))