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.
Solved! Go to Solution.
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.
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.
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))