cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lwhal
Frequent Visitor

Help with Heatmap in a matrix visual

Hello, 

 

I am hoping someone can help me with a solution to my problem. I was asked to use a matrix to create a heatmap. I have done it before but this particular report is displaying the data and the color will corespond to the maturity of the data. I have added a flag to the underlying data that 0-Not Mature 1-Maturing 2-Mature. Lets say I have 2019 data 2 of the values have a 0 flag but the third has a 1 to inducate it is maturing the color will stop at 0 will not color the next value. My moc data below will show the issue I am having. The maturity flag is what I am using to indicate the background color here is the measure I am using 

Maturity color = SWITCH(SELECTEDVALUE(years[maturity]),
"0" , "#5DADE2",
"1" , "#85C1E9",
"2" , "#AED6F1"
)

Heatmap.png

 

 This is what I would like to see in the pbi 

 

heatmap2.png

 

 

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User III
Super User III

Two comments:

 

You can simplify your logic like this

Maturity color = SWITCH(SELECTEDVALUE(years[maturity]),
"2" , "#AED6F1",
"1" , "#85C1E9",
"#5DADE2")
 
But this will still not help you with the cells where there is no "SELECTEDVALUE"  (missing data).  For that you may need to create dummy data points (via a cross join)  or use a fake matrix background color set to  #5DADE2.

View solution in original post

3 REPLIES 3
PSReneKA
Regular Visitor

Hi Lwhal,

Perhaps another quick option would be to use the Conditional Formatting option on both the % and Maturity columns and use the Diverging setting to get your three specific colors (btw - I think the color codes may be off from what is seen on screen), I just used three quickly selected blues in the attached PBIX example.

Select your matrix and under formatting (paintbrush) open the Conditional Formatting options:

Select the % field and turn the background color to ON, then click the Advanced Controls, and a new pop-up will be displayed:

For your example given you only have 3 values, you really could just use the Lowest and Highest settings at 0 and 2, respectively, but since you want to control the three colors, click on the Diverging checkbox.

In the three drop-downs, change the values (lowest, middle, highest) to Custom, and you will be able to enter your 0, 1, and 2.  Set your colors to your choosing. 


Based on your request to keep the blanks in the dark blue, make sure to keep in the Advanced setting, keep the Default formatting set to "As zero".  If you wanted those a different color, you can control it there.

Click OK to apply these changes to the % columns and then select the Maturity field, and repeat the steps for the conditional formatting for those columns. 

Let me know if you want a sample PBIX to review...I hope this helps and Happy Holidays!

ConditionalFormatting_menu.jpg

 

lbendlin
Super User III
Super User III

Two comments:

 

You can simplify your logic like this

Maturity color = SWITCH(SELECTEDVALUE(years[maturity]),
"2" , "#AED6F1",
"1" , "#85C1E9",
"#5DADE2")
 
But this will still not help you with the cells where there is no "SELECTEDVALUE"  (missing data).  For that you may need to create dummy data points (via a cross join)  or use a fake matrix background color set to  #5DADE2.

View solution in original post

lwhal
Frequent Visitor

Thank you so much it worked great.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors