3 Replies Latest reply on Apr 10, 2014 10:22 PM by Prashant Sharma

    filtering without affecting table calcs

    Tom White

      Here's what I'm trying to achieve. I have a table with data about different companies. The fields are company(the name), metric_id(name of metric), metric(value of metric). For each company I am calculating it's percentile rank for each metric. I have the percentile calculation working fine, if I exclude certain companies, then the percentiles are automatically re-calculated. So far, so good.

       

      Now, what I want to do, is use a filter to select a single company from my dataset and display it's percentile rank. But, when I do that, it re-calculates the percentile as 100, since now there is just 1 company in my dataset. Is there a way to stop this from happening ?

       

      I'm attaching some sample data. Sheet 1 shows the raw data and the percentile.

       

      Sheet 2, is the sample selection sheet, I would use this to pick a subset of my population for analysis.

       

      Sheet 3, is the final report I'm  trying to create. You select a company and metric and see the metric value and percentile.  But the percentile always ends up as 100, not the value from Sheet 1.

       

      Any advice or suggestions on how to do this would be greatly appreciated !