2 Replies Latest reply on Sep 26, 2016 5:01 AM by Chris Lyde

    Looking to hide/exclude top and bottom percentiles for data outside a set (but not in it)

    Chris Lyde

      Hi,

       

      I have attached a dummy workbook which I have put together to show the problem I am looking to solve.

       

      I am aiming to hide or exclude data above the 90th percentile and below the 10th percentile of the benchmark data (but still show all the data for the selected department). The main reason for this is that the dataset that I have includes confidential benchmark information.

       

      The 'Using ref lines' sheet shows the way I have gone about this before by using reference lines > percentiles, but the issue here is that to include all of this data, the axis will need to be set at the highest point of data, which is what I am trying to hide.

      (I still need it to go this high though if the department selected has data this high).

       

      Explanation of what the graph is showing:

       

      I have shown the range of salaries for different job levels in different departments by using a parameter. Essentially, this allows you to select a department and it will show the range of salaries within this in one colour, compared to all other departments in another colour.

       

      I have used sets to show this and have also included averages for those within the set (department in this case), and as a whole (by using reference lines).

       

      If there are any points that you'd like clarifying, please let me know.

       

      Any help would be greatly appreciated.

       

      Thanks

       

      Chris