Showing results for 
Search instead for 
Did you mean: 
Advocate I
Advocate I

Conditional formatting in a matrix when drilling down

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.



Jimmy Tao

View solution in original post

Community Support
Community Support

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

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.



Frequent Visitor

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.



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

@kelseyp ,


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



Jimmy Tao

Helpful resources

September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors