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

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors