Vivek Bhat Posted February 12, 2021 Share Posted February 12, 2021 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. Link to comment Share on other sites More sharing options...
Fabian Duerr Posted February 12, 2021 Share Posted February 12, 2021 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): Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now