Conditional formatting in a matrix when drilling down

[kelseyp]

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.

[v-yuta-msft]

@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]

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]

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))

[v-yuta-msft · Accepted solution]

@kelseyp ,

I'm afraid currently this can be reached. You can submit your idea here.

Regards,

Jimmy Tao