Waiting in hurry: Background color doesn't work correctly with 4 levels hierarchy in Matrix Table

[elliejiang20]

There are 3 hierarchy levels for a Matrix table.

Why does background color which is controlled by Calculated column DAX and applied in conidiation formatting not take effect on the 2th level hierarchy, but works well after drill down to the 3th level hierarchy?
Not take effect means the background color can’t show correctly as per below background color DAX logic, the cell should be green shows red, red shows green:

(--#8DDCA0 green, #FD625E is red in below---)
BackgroundColor_Flag =

IF (TABL[Category]="NNA (in percentage)", IF(TABL[Value]>=95,"#8DDCA0","#FD625E"),

IF(TAB[Category]="#of missed",IF(TABL[Value]=0,"#8DDCA0","#FD625E"),IF(TABL[Value]<=15,"#8DDCA0","#FD625E")))

By the way, the "Values" in Matrix table configuration applied an another calculated column DAX to show some values in percentage, some in Number as below:

Valuewith% = if(TABL[Category]="NNA (in percentage)" ,FORMAT(TABL[Value]/100, "0.0%") ,Format(TABL[Value],"General Number"))

"Format style" in conditional formating is 'Filed Value' with DAX BackgroundColor_Flag, the "Summarization" is 'First'‘Last’. Whichever options for Matrix table and conditional formattting, background color still does work on the 3th level hierarchy. 

Please help to investigate.

[elliejiang20]

Not reslove yet

[VijayP]

@elliejiang20 
IF Formula doesnt look good! for each IF you need to give one result where as you have given the result after two if conditions , and this is causing confusion in DAX engine! Chekc that,

Better use Switch Function for better formula Construct!

[elliejiang20]

I just changed to Switch as below, the problem is till there, pls see attachement picture.

Tried 1):

BackgroundColor_Flag =

SWITCH(TABL[Category],

"NNA (in percentage)", IF(TABL[Value]>=95.0,"#8DDCA0","#FD625E"),

"#of missed",IF(TABL[Value]=0.0,"#8DDCA0","#FD625E"), ---#8DDCA0 is green

"Premium", IF(TABL[Value]<=15.0,"#8DDCA0","#FD625E"),

"Operation", IF(TABL[TTAValue]<=15.0,"#8DDCA0","#FD625E"))

Tried 2):
By the way, the "Values" in Matrix table configuration applied an another calculated column DAX to show some values in percentage, some in Number as below, so Valuewith% column is text, don't think Dax can evaluate greater than and less than.

Valuewith% = if(TABL[Category]="NNA (in percentage)" ,FORMAT(TABL[Value]/100, "0.0%") ,Format(TABL[Value],"General Number"))
So I change DAX to:

BackgroundColor_Flag = ---so Valuewith% column is text, don't think Dax can evaluate greater than and less than.

SWITCH(TABL[Category],

"NNA (in percentage)", IF(TABL[Valuewith%]>="95.0%","#8DDCA0","#FD625E"),

"#of missed",IF(TABL[Valuewith%]="0.0","#8DDCA0","#FD625E"),

"Premium", IF(TABL[Valuewith%]<="15.0","#8DDCA0","#FD625E"),

"Operation", IF(TABL[Valuewith%]<="15.0","#8DDCA0","#FD625E"))

Then looks below, not correct: