Using DAX for Conditional Formatting

[KMcCarthy9]

I am trying to create the table below in PBi (without the Ref Target column). I am having 2 issues, but they are linked:
1. Trying to control conditional formatting using DAX. I am currently using the DAX formula below:
     

Backlog Color Formatting =

VAR _30days = (CALCULATE(

            CALCULATE(COUNTA('workorder'[Backlog Timeframe]),

        'workorder'[Backlog Timeframe] = "Within 30 Days") / CALCULATE(COUNTA(workorder[WorkOrder Number]))))

VAR _60days = (CALCULATE(   

        CALCULATE(COUNTA('workorder'[Backlog Timeframe]),

        'workorder'[Backlog Timeframe] = "31 - 60 Days") / CALCULATE(COUNTA(workorder[WorkOrder Number]))))

VAR __61days = (CALCULATE(  

        CALCULATE(COUNTA('workorder'[Backlog Timeframe]),

        'workorder'[Backlog Timeframe] = "61+ Days") / CALCULATE(COUNTA(workorder[WorkOrder Number]))))

RETURN

SWITCH(TRUE(),

_30days <=.84, "#C6EFCE",

_30days >= .85,"#FFC7CE",

_60days <=.09, "#C6EFCE",

_60days >= .10,"#FFC7CE",

__61days <=.04, "C6EFCE",

__61days >= .05,"#FFC7CE",

_Blank, "#FFC7CE")

This is putting the colors into the visual but not correctly. It currently gives me the following output:

I have played around with the order of the Switch statement, but it either gives me the top as green and the rest red, or the top red and the rest green. Nothing according to the percentages. I am using "show as percent of column total" to get the percentages. 

Secondly, I need to add into that DAX statement whether or not it is considered Backlog or Closeout, since the reference targets are different depending on the group. 

To sum up, I need to use conditional formatting to show red or green based on whether it's hitting the specified reference target and backlog or closeout. Is this possible? 

I am no PBi expert, so please bear with me. I appreciate any advice. Thanks!

BONUS POINTS - If you can figure out how to make the blank cells either say N/A, 0.0% or grayed out? 

[Mmiller212]

Good afternoon - I am trying to use the logic below, and it appears to work well, but when I go to change the background format to use my new Color measure, its not able to be selected.  Any idea what I might be doing wrong?

[Mmiller212]

Solved my issue - Have to set it as a text field.  Works great!

[KMcCarthy9]

Sorry I was late seeing this. Glad you figured it out! 

[Cmcmahan]

Where are you entering/using this DAX? I was unaware you could control the colors of cells directly with a measure.  Could you share an example .pbix of your current setup? Even if you have to fake some data, it would be very useful.

For the bonus point question, what measure(s) are you currently using to fill in this table? Usually just adding +0 to the measure will add a zero instead of a blank to a table.

[KMcCarthy9]

Hi Cmcmahan, In order to use a measure for conditional formatting, you need to go to the conditional formatting within the formating section of your visual. From there you want to choose Field Value, and then place the measure you are using in the Based on Field. See below:

Hope this helps. 

[Cmcmahan]

AHH, that's how that is supposed to work.  I've always used rules, to format fields.  

Could you share some sample data that we can copy/paste? Scrub names/proprietary info if you need to, but figuring out issues (especially with aggregations) is always MUCH easier with a test sample of raw data.

If not, let me know, and I can try and fake up some data that will probably(?) behave the same.

[KMcCarthy9 · Accepted solution]

Sorry I didn't get a chance to put up some sample data. I was able to figure it out.  
First I needed a measure to get the values I needed:

          % of Total Workorders =

          VAR _FilterCount = COUNTROWS('workorder')

         VAR _AllCount = CALCULATE(COUNTROWS(workorder),ALLSELECTED(workorder))

         RETURN

         DIVIDE(_FilterCount,_AllCount)

Then I used this measure to place in the Conditional Formatting Field Value:
               

% of Total Workorder Color =

VAR _30DaysBL = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="Within 30 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Backlog"),True,False)

VAR _30DaysCO = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="Within 30 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Closeout"),True,False)

VAR _31DaysBL = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="31 - 60 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Backlog"),True,False)

VAR _31DaysCO = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="31 - 60 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Closeout"),True,False)

VAR _61DaysBL = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="61+ Days",SELECTEDVALUE(workorder[Backlog Status Group])="Backlog"),True,False)

VAR _61DaysCO = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="61+ Days",SELECTEDVALUE(workorder[Backlog Status Group])="Closeout"),True,False)

RETURN

SWITCH(

TRUE(),

_61DaysBL && [% of Total Workorders] <=.049, "#C6EFCE",--Green

_61DaysBL && [% of Total Workorders] >= .050,"#FFC7CE",--Red

_31DaysBL && [% of Total Workorders] <=.099, "#C6EFCE",

_31DaysBL && [% of Total Workorders] >= .100,"#FFC7CE",

_30DaysBL && [% of Total Workorders] >= .850,"#C6EFCE",

_30DaysBL && [% of Total Workorders] <=.849, "#FFC7CE",

_61DaysCO && [% of Total Workorders] <=.300, "#C6EFCE",

_61DaysCO && [% of Total Workorders] >= .319,"#FFC7CE",

_31DaysCO && [% of Total Workorders] <=.700, "#FFC7CE",

_31DaysCO && [% of Total Workorders] >= .719,"#C6EFCEE",

_30DaysCO && [% of Total Workorders] >= .719,"#C6EFCE",

_30DaysCO && [% of Total Workorders] <=.700, "#FFC7CE",

"#FFF000")--Yellow

Thank you for your attempted assistance.