I have a matrix which has color scale formatting. It works fine at top level, however when I drill down using either the +/- buttons or Expand all down one level in the hierarchy button, the formatting doesn't work as I expected because it still refers to the top level for lowest and highest values (see screenshot below). Is there any way I could fix this? Thank you.

kelseyp , I'm afraid currently this can be reached. You can submit your idea here. Regards, Jimmy Tao

Conditional formatting in a matrix when drilling down

4 Replies

v-yuta-msft
Community Support
7 years ago
kelseyp ,

The background color will be changed after drill down on my side. Have you set the lowest value or highest value like below?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- kelseyp
  Advocate I
  7 years ago
  Hi v-yuta-msft,
  My current settings:
  
  I have figured out that it is because there is nothing under Non-Managed (see below). Is it possible to restrict the conditional formatting to expanded data only? Thank you for your help.
  - Anonymous
    Not applicable
    4 years ago
    I know this is old, but I think it is colorizing those drill downed values, but when the highest value is super high it skews the color gradient. I just had that problem too. What I did was create a dax formula to smooth out the skewed variance and force the lower values to have color, so only the 0 values are white.
    
    Then you would use that for your "Based on Field" value instead of the column you are actually colorizing.
    
    Example:
    first, get the z score of you values
    Apply logs to further smooth out
    then force the lowest values to have something that will trigger a color.
    
    smoothed = var smooth1 = if([Change in Sales%]>0,([Change in Sales%]-AVERAGEX('Proj Sales',[Change in Sales%]))/(STDEVX.P('Proj Sales',[Change in Sales%])*3),[Change in Sales%])
    var smooth2 = IF(smooth1>0,LOG(smooth1+1,10),IF(smooth1<0,LOG(ABS(smooth1)+1,10)*-1,0))
    var smooth3 = IF(smooth1>0,LOG(smooth1+1,10),IF(smooth2<0,LOG(ABS(smooth2)+1,10)*-1,0))
    var smooth4 = IF(smooth3>0,LOG(smooth3+1,10),IF(smooth3<0,LOG(ABS(smooth3)+1,10)*-1,0))
    var smooth5 = IF(smooth4>0,LOG(smooth4+1,10),IF(smooth2<0,LOG(ABS(smooth2)+1,10)*-1,0))
    return IF(and(smooth5<=.01,smooth5>0),.01,IF(and(smooth5>=-.01,smooth5<0),-.01,smooth5))