Formatting help with heatmap

I inherited a heat map representing steps in a hiring process. First, I had to figure out how to create a heat map. But I want to make it better than what they had. So I updated the data and used conditional formatting so that the colors have a consistent meaning.

There are two goals. One is our MOP goal and the other is our AGENCY goal. The current map compares each number in the column to the AGENCY for that step. I’d love to have:

a)A way to push a button and show performance in comparison to the AGENCY goal or the MOP goal. Or something similar. I included an image for the goals.

b)A more visually appealing color scheme

c)A legend

Green = On or better than goal
Yellow = 3 days or less above goal
Red = 4 or more days above goal

If you can help or point me to the videos/courses that can teach a newbie about these things, I’d be most grateful.3rd DRAFT_Hiring_TotalHires.pbix (784.0 KB)

@talk2gwhite,

I think I can help you with all three of these questions:

  1. I would do this with a toggle visual tied to a page navigation button action. Basically, build your page the way you want for the agency goal, then duplicate that page and change the relevant measures, column references, etc. to align with the MOP goal. Have the switch/toggle visual in one position for the first goal and in the opposite position on the second page for the other goal. Then set your page navigation button click to bring you to the other page.

It’s much easier than it sounds - take a look at my Data Challenge #4 entry PBIX file - I used this technique to toggle between the table view and the chart view.

@sam.mckay did a video explaining this technique in detail:

  1. Custom legend – another great video from Patrick at GIAC on how to do this:
  1. Building attractive color themes:

I wrote a long post about this earlier this summer, which I think is a good place to start since it references a bunch of excellent resources for color theme generation:

Sam also did an excellent video on this topic earlier this week:

I hope this is helpful. Please let me know if you have any questions about the above.

  • Brian

P.S. You and I should talk off-line at some point – it sounds like we do some very similar things within the Federal Government, and I’ve been building a number of reports addressing the same HR/process tracking issues.

2 Likes

This is great! I love this actually. If you are doing similar work I’d love to connect. How do we do that without blasting our contact info out into the Forum?

1 Like

@talk2gwhite,

Per our conversation, there are some simple things you can do to make that heatmap more attractive. First, though I will say that for someone new to Power BI, there’s a lot to like about what you’ve done with this report. I work with this same dataset every week, and the data you’ve chosen are highly relevant, actionable and well-organized. The heatmap structure is a good choice for visualizing this data. The main problem is that the color scheme is awful, but fixing it is easy.

  1. understand how colors work – I am not a graphic designer, and there many folks on the forum who can run circles around me re: design, but I’ve learned the basics of color theory and how to get my reports to look decent. I recommend going to the Data Challenge gallery and looking through the entries to get ideas on how to improve your colors and design elements.

This article goes through the fundamentals of color usage, and explains why we find some color combinations, hues, saturation levels etc. attractive versus not:

  1. choose colors that make sense - in the video from @sam.mckay that I referenced above, he always asked the question whether the colors chosen makes sense in the context of the data depicted? In the case of a heatmap, I think it makes the most sense to pick a color palette that goes from “cool” (good) to “hot” (bad). Microsoft, in the built-in themes gallery, has a theme called Temperature that works perfectly for this purpose.

Now with a little work picking complementary colors to make the headers and totals pop, and cleaning up the formatting, we can easily go from this:

To this::

I’m sure the design experts on the forum can do better than that, but I think overall that’s now a pretty good-looking report.

I hope this is helpful. Full solution file attached below.

  • Brian

3rd DRAFT_Hiring_TotalHires makeover solution.pbix (757.5 KB)

3 Likes

There is one more item I need to add. We have goals for each one of those columns. I want to add the goals under each step so that you can see what the HUD AVG is right above the goals. I was thinking of adding a static image, but I don’t know how to make sure that the column widths are the same. From the attached screenshot I want to add the AGENCY GOALS under the HUD AVG. Any thoughts?

Any ideas?

@talk2gwhite,

I would definitely not recommend doing this as a static image. In addition to the problem of getting it to line up and look consistent, more importantly you lose all ability to use the goal information dynamically to calculate performance measures of actual relative to goal. I would create a separate supporting table with your goal information in it, and bring that table via “Get Data” into your data model. That way, you can both add that information to the bottom of your report via matrix or table visual, and also use it dynamically in Power Query, calculated columns and measures.

  • Brian
2 Likes

Amazing value add here Brian

1 Like

So, I put the values in a table and pulled them in. See screenshot. There is one more thing I need some feedback on. This project was a revamp of an old Power BI format. Screenshot attached. One of the things the old version had, that mine currently does not, is some way to visually represent those involved in the process.

The screenshot will show four “buttons” at the bottom. They are static images. At the top you will see corresponding colors. The colors let you know which of the four entities were involved in each step. I need that. But the colors don’t fit with my new color scheme and I don’t see where I can slide them in without them being so intrusive. My background was created in PowerPoint and saved as an image.

Thoughts?

@talk2gwhite,

The report is shaping up nicely. Personally, I would tone the hue of the red down a bit, and center the numbers, but major improvement over the report you inherited.

In thinking about how to add the color key you want, I was going to write a cool DAX measure to do this automatically, but then I looked at the data model you inherited for the first time, and now feel like the home inspector saying “the new paint job on your house looks great, but unfortunately the foundation is completely cracked and needs to be completely rebuilt”. In terms of the data model, the tables are not connected properly, not structured properly, the fact table is composed almost entirely of calculated columns (I’ve literally never seen that before), needed dimension tables are missing - the whole thing basically needs to be blown up and redone if you’re going to be doing substantially more analysis with this, since writing proper DAX off of such a flawed data model is going to be nearly impossible IMO.

I’m sure this is not what you want to hear, so there are two options to proceed:

  1. if you are under a tight time constraint and need to produce something right away, I would suggest adding the color key manually in the white space between the title and the table in Powerpoint as part of your template and then just reattaching it as your page background in PBI when that is done.

  2. If you have the time, using the eDNA data modeling training content and tapping the expertise on the forum, completely rebuild your data model into a proper star schema that will support the full range of analyses you may want to do.

I hope this is helpful, even if not what you wanted to hear.

  • Brian
1 Like

It definitely helps!

@talk2gwhite,

One other caveat – if you choose to go the first route, I would suggest doing some heavy duty QA/QC on your numbers to validate them before you circulate the report, since even correct DAX can produce inaccurate results when operating on a broken data model.

  • Brian
1 Like