Conditional Formatting for % of Column Total - Not Working

I was able to achieve conditional formatting in a matrix by first creating the measure below:

% of Total Workorders =

VAR _FilterCount = COUNTROWS('workorder')

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

RETURN

DIVIDE(_FilterCount,_AllCount)

Then applied this measure to the conditional formatting by 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

This works correctly but gives the percentage for the entire visual, when I would really like to show it by % of column total. When I select "Show as % of CT" the conditional formatting does not change and is not correct. Is it possible to use a measure for conditional formatting while showing % of CT?

Need Help

v-lili6-msft

7 years ago

hi, KMcCarthy9

1. For your requirement, you want "% of CT" in the matrix, you should use ALLSELECTED([Backlog Timeframe]) in formula like below:

% of Total Workorders = 
VAR _FilterCount = COUNTROWS('Backlog Trend Data')
VAR _AllCount = CALCULATE(COUNTROWS('Backlog Trend Data'),ALLSELECTED('Backlog Trend Data'[Backlog Timeframe - All Time]))

RETURN

DIVIDE(_FilterCount,_AllCount)

and you said it gives you the result of 100% in each cell.

So there should be a dim Backlog Timeframe table, please replace ALLSELECTED(workorder[Backlog Timeframe]) with it.

2. In this sample pbix, I find that there is a logic error in your formula:

% of Total Workorder Color = 
VAR _30DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False)
VAR _30DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False)
VAR _31DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False)
VAR _31DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False)
VAR _61DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time])="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False)
VAR _61DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="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] >= .010,"#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] >= .710,"#C6EFCEE",
_30DaysCO && [% of Total Workorders] >= .710,"#C6EFCE",
_30DaysCO && [% of Total Workorders] <=.700, "#FFC7CE",
"#FFF000")--Yellow

As the red part, all the values [% of Total Workorders] are greater 0.10 and less than 0.99 will all show as red("#FFC7CE"). please adjust it.

For example:

If less than 0.4 is green or greater than 0.5 is red

% of Total Workorder Color = 
VAR _30DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False)
VAR _30DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False)
VAR _31DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False)
VAR _31DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False)
VAR _61DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time])="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False)
VAR _61DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False)

RETURN

SWITCH(
    TRUE(),
_61DaysBL && [% of Total Workorders] <=.49, "#C6EFCE",--Green
_61DaysBL && [% of Total Workorders] >= .50,"#FFC7CE",--Red
_31DaysBL && [% of Total Workorders] <=.39, "#C6EFCE",
_31DaysBL && [% of Total Workorders] >= .40,"#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] >= .710,"#C6EFCEE",
_30DaysCO && [% of Total Workorders] >= .710,"#C6EFCE",
_30DaysCO && [% of Total Workorders] <=.700, "#FFC7CE",
"#FFF000")--Yellow

Result: