Calculating the average of values in the view

[Vivek Bhat]

I am trying to calculate the average of the a column but the rows are derived by calculating the maximum of a group of values. The maximum values for each group is displayed in the cross table but while calculating the average it is considering all the data instead of calculating the average of max values.

 

Here's a snip of the data, the values 20, 12,15 and 70 are the max values calculated. While calculating the average it comes up to 24 instead of 29.25. It is considering the underlying rows which is not part of the view to calculate the average.

[Fabian Duerr]

It's a bit tricky to answer this without knowing your data structure...

But you might be able to solve this by limiting your data in the cross table with an expression to ensure that each MAXvalue gets equal weight.

Below you will find an example where I used a Rank() function. The calculated column is just to show you what this boolean expression is doing:

Rank([Type],"ties.method=first",[Type]) = 1But keep in mind that your cross table also depends on the current filtering scheme. But you can turn off filtering in the cross table as well.

 

Turn off filtering in cross table (to ensure not losing Rank = 1):