I am trying to create a filter based on a column, mentioned below is the code
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")))))
User | Count |
---|---|
108 | |
63 | |
59 | |
37 | |
37 |
User | Count |
---|---|
117 | |
65 | |
64 | |
63 | |
50 |