Aggr error in chart: only displaying 1 value per dimension

[Anonymous]

Hey all,

I have a list of queries, each query is part of a bucket. I want to use an if statement to assign the average monthly volume of the query's bucket if its own monthly volume is null, and if it's not null just return the query's volume. Here's the formula:

if(isnull(avg([monthly volume])), aggr(avg({1}[monthly volume]), bucket_v2), avg([monthly volume]))

And the average monthly volumes by bucket are:

However, this is the result:

The column [monthly vol] is just there to show that all are null for the query except "online colleges", so the [adj monthly vol] should be the buckets' averages for all except "online colleges". As you can see it's only showing the bucket average for one of each bucket, the rest are null.

Can anyone help me figure out what I'm doing wrong?

[sunny_talwar · Accepted solution]

Try this

If(IsNull(Avg([monthly volume])), Aggr(NODISTINCT Avg({1}[monthly volume]), bucket_v2), Avg([monthly volume]))

or this

If(IsNull(Avg([monthly volume])), Avg(TOTAL <bucket_v2> {1}[monthly volume]), Avg([monthly volume]))

[Anonymous]

The NODISTINCT worked like a charm. Can't believe I didn't think of that. Thank you Sunny!

[sunny_talwar]

Glad it worked out for you, but I wonder if you tried the other one also and if it did not work?

[Anonymous]

The other one worked but with the {1} set identifier included, it returns all rows disregarding my selection. When I remove the {1} it just returns nulls like before. So the first solution with NODISTINCT is better Thanks again!