It’s a loooong title (well, it was «Conditional Formatting with divergent color gradient for values and totals»), but it’s exactly what I wanted to achieve when I discovered the limitations of the conditional formatting options in the GUI of Power BI Desktop. The key words are «dynamic» in one hand and «values and totals in the other» I know, some of you may think I’ll be doing some copy-cat article of the crazy video from Bas in which he shows how to use the undocumented (that I know of) function hsla(). A function that you need to leave as text?!? Crazy, just crazy. But no, I take a complete different approach and I think it’s worth the effort! Of course you can take ideas from both me and Bas and come up with your own unique approach for dynamic conditional formatting.
If you have worked with Matrix visual you probably have faced the problem: Your end user would like to group all the measures that are thrown in into neat little groups, putting all de Quantity- related measures in one group, all the value measures in another, for example. What I’ve seen so far is that people just struggle with the fact that this is not possible in the Matrix visual, and add text boxes on top, as if the header was indeed a two-row header. But it’s not. And if you have a large matrix that requires horizontal scroll, you’ll know what I am talking about. Can we do any better? Yes. Are calculation groups involved? You bet. Difficult DAX? Not at all!
Ok, I have maybe gone a bit overboard with the title, but I think it has some advantages over other hacks out there to establish the column widths of matrix, so here I am to explain how I came up with it and how to use it.
The first hack I saw was from Ben Ferris (aka The Power BI Guy) which added a dummy measure with a number of 0 to make the width (having automatic width enabled) and then it would disable automatic widths and remove the measure. Nice. But of course, if new columns appear you’ll need to set the thing again. Something similar happens with the approach of Bas, who skips the dummy measure thing and instead just plays with the format string to show the evenly wide number. His approach is cooler because you skip the measure thing, but you have the same weak points.
However, Bas’s video got me thinking on the topic and the role of format strings…