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",...
Need Help
Show and Tell
Tutorial Requests
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