Changing Conditional Formatting Based on Rules

[NJ81858]

Hello,

I have a report that shows the percentage of sales compared to customers talked to, and the current goal for this is 20%. In Q2, this goal is changing to 25%, and hence I need to change my conditional formatting to reflect this change, but there is a wrinkle in the change. Even though the data for Q2 and beyond is changing to a 25% goal, the Q1 goal still needs to be 20%, and show Green when that goal is met. 

Here is my visual in it's current state, showing Green for anything >= 20%:

Here is the conditional formatting for the right-most column:

The measure for this right-most column is:

Percentage = IF ( AND ( [# of Customers] = 0, [# of Sales] = 0 ), 0, IF ( [# of Customers] = 0, 1, DIVIDE ( [# of Sales], [# of Customers] ) ) )

All this does is divide the # of Sales by the # of Customers, and if they are both 0, then it shows as 0, and if they didn't talk to any customers then it shows as 1, as they may get Sales allocated to them without actually talking to a customer.

Here is my Date Table, note that my weeks begin on Saturday and end on Fridays, so the Start of Week and End of Week are different, and the Month/Quarter is given to what the date is at the end of the week, for example,  February 25 to March 3 would be allocated to March, as the end of the week is in March:

Also the fields that have slicers attached to them are Quarter, Month Name, and Date Range.

My suspicion tells me that this is a simple task to solve, but I am not sure how to solve it. Thank you in advance!

[ahmedoye · Accepted solution]

Which Date Field do you have on the report table? If you have anyone there you should try the approach below. If it works, kindly mark this as a solution to allow anyone who may have similar issues find it easily:

1. Create a Measure as:
    IF(SELECTECTEDVALUE(DateTable[Quarter]) = "Q2" && [YourMeasure%] >= 0.25, 1,
    IF(SELECTECTEDVALUE(DateTable[Quarter]) = "Q2" && [YourMeasure%] >= 0.20,1,
    0))

2. With the above Measure, create a Conditional Formatting using "Field Value" option. Set 1 to Green and 0 to Red

[NJ81858]

@ahmedoye  Thank you so much! This ended up working, I just had to add an additional IF statement, as my table defaults to all dates, so I just added a 0.5 value and set that option to Gray in the Conditional Formatting.

My final measure was:

IF(SELECTEDVALUE('Date Table'[Quarter]) = BLANK(), 0.5,
IF(SELECTEDVALUE('Date Table'[Quarter]) <> "Q1" && [Percentage] >= 0.25, 1,
IF(SELECTEDVALUE('Date Table'[Quarter]) = "Q1" && [Percentage] >= 0.2, 1, 0)))