Set Analysis on combo chart

[Bikoman]

Hey guys,
I got a combo chart with some calculations with rangeavg (which I assume the problem is there).

The Infestation Level formula looks like this:  rangeavg(above(sum (hasHigh)/count(hasHigh),0,15))*100

the thing is only the bar chart on the left show the right data once I drill down, lets say the third bar chart (id - 6086) the data changes. I think I need set analysis to set the formula to look for each id alone.
thanks in advance.

[Rodj · Accepted solution]

Hi @Bikoman ,

It's a little difficult to tell just what you are trying to achieve but I'm assuming you have drilled through a dimension to a child dimension to get the image you've included here? It appears you are trying to calculate an average of averages, that's generally not going to end well as it isn't going to give you the same number as the true average calculated at the lowest level of cardinality. You have your first calculation of an average:

sum (hasHigh)/count(hasHigh)

Then you are doing a rangeavg to it. This is just averaging the averages, rather than the actual figures.

If that's what you are trying to achieve then all good. Set analysis is useful if you want to restrict your calculation to a set of values, but I'm not sure that is what you are trying to do.  It's possible that using aggregation (see the help for the "Aggr" function) might be what you are after as it will allow you to aggregate the averages at the correct level. Putting this kind of data into a table first can be a good way of getting your head around what is happening.

Cheers,

Rod

[Bikoman]

worked perfectly thanks!

I used this in the formula:
rangeavg(Aggr( above( sum (hasHigh) / count (hasHigh),0 ,15),something)) * 100