cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kelseyp
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.

00.png01.png

1 ACCEPTED SOLUTION

@kelseyp ,

 

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

 

Regards,

Jimmy Tao

View solution in original post

4 REPLIES 4
v-yuta-msft
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?

Capture.PNG 

 

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:

00.png

 

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.

 

01.png

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

 

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

@kelseyp ,

 

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

 

Regards,

Jimmy Tao

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors