Conditional Formatting Help

[michellem0771]

Hello,

I have a report that shows revenue for this year and revenue for the same period last year.

I want to dynamically highlight the clients that fell off the TopN from the prior year and highlight those clients that are new to the TopN list this year. 

I was successful in getting the measure correct, except when the resulting rank is less than the TopN.  

The measure to identify the records is:

T_Top25_S1 = IF(

                ([TopN Client Ranking by Client SPLY] > [TopN Value] || ISBLANK([TopN Client Ranking by Client SPLY]))

                    && [TopN Client Ranking by Client] <= [TopN Value]

                        ,"New",

             IF(

                [TopN Client Ranking by Client SPLY] <= [TopN Value]

                    && ([TopN Client Ranking by Client] > [TopN Value] || ISBLANK([TopN Client Ranking by Client]))

                        ,"Fall Off",

             IF(

                [TopN Client Ranking by Client SPLY] <= [TopN Value]

                    && [TopN Client Ranking by Client] <= [TopN Value]

                        ,"Remain"

                        )

                )

        )

With the conditional formatting measure here:

Top25 CF =

SWITCH([T_Top25_S1],"New","#11c214","Fall Off","#e6003b","Remain","#000000")

My TopN filter is between 0 and 100 in increments of 5.

When selecting Top 25 with no other filters, the data is correct. (It is always correct if the resulting data is greater than the Top N.)

When I filter by a specific group, and keep the Top N at 25 or greater and the results are less than the Top N, the conditional formatting is not working.

 

I see that my measure doesn't account for those records where the Rank is less than the Top N, but everything I try isn't working.

Can anyone help me with this please?

Thank you!

[michellem0771 · Accepted solution]

I was able to correct this issue by coding the rankx to exclude any null values for current year / prior year revenue.

Once I did that, the logic on my query above corrected and the conditional formatting reacted as expected.