Forum Discussion

nniphadk's avatar
nniphadk
Frequent Visitor
3 years ago

Creating a less than equal to filter

I am trying to create a filter based on a column, mentioned below is the code

 

Benchmark Filter =
IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] >= 1,"Greater than 100th Percentile",
IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 1, "100th Percentile",
IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.90, "90th Percentile",
IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.80, "80th Percentile",
IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.70, "70th Percentile",
IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.60, "60th Percentile and less","Default"))))))
 
When I add this to the filter it only shows option 1 and 2
What am I doing wrong?

Need Help
Show and Tell
Tutorial Requests

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    That is because either the first or the second check are always true.  If the amount is <= 0.60 it is also <= 1 so you have to reverse the order of the checks after the first one.

     

    Benchmark Filter =

    IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] >= 1,"Greater than 100th Percentile",
    IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.60, "60th Percentile and less","Default"),
    IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.70, "70th Percentile",
    IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.80, "80th Percentile",
    IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.90, "90th Percentile",
    IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 1, "100th Percentile")))))

    • nniphadk's avatar
      nniphadk
      Frequent Visitor
      3 years ago
      Benchmark Filter =

      IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] >= 1,"Greater than 100th Percentile",
      IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.60, "60th Percentile and less",
      IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.70, "70th Percentile",
      IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.80, "80th Percentile",
      IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 0.90, "90th Percentile",
      IF(Master[ADJ 10M + ORG1 PTC]/Master[Doc,R1,Public-22-23] <= 1, "100th Percentile"))))))

      I had to change it up a bit because the too many arguements were passed

      But when I am hoping to pick to 70th percentile and less, I am hoping it picks up everyone who falls below 70